-- =============================================
-- 客户管理模块数据库初始化脚本
-- V1__init_customer_tables.sql
-- =============================================

-- 客户表
CREATE TABLE IF NOT EXISTS `customers` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '客户ID',
    `tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
    `customer_code` VARCHAR(50) NOT NULL COMMENT '客户编码（唯一）',
    `customer_name` VARCHAR(200) NOT NULL COMMENT '客户名称',
    `short_name` VARCHAR(100) DEFAULT NULL COMMENT '客户简称',
    `level` VARCHAR(20) DEFAULT 'NORMAL' COMMENT '客户等级：VIP/POTENTIAL/NORMAL',
    `customer_type` VARCHAR(20) DEFAULT 'COMPANY' COMMENT '客户类型：COMPANY/INDIVIDUAL',
    `industry` VARCHAR(100) DEFAULT NULL COMMENT '所属行业',
    `phone` VARCHAR(50) DEFAULT NULL COMMENT '联系电话',
    `backup_phone` VARCHAR(50) DEFAULT NULL COMMENT '备用联系电话',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '电子邮箱',
    `website` VARCHAR(200) DEFAULT NULL COMMENT '官方网站',
    `business_license` VARCHAR(50) DEFAULT NULL COMMENT '统一社会信用代码',
    `registered_capital` DECIMAL(15,2) DEFAULT NULL COMMENT '注册资本',
    `source` VARCHAR(50) DEFAULT NULL COMMENT '客户来源',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '客户状态：ACTIVE/INACTIVE/BLACKLISTED',
    `rating` INT DEFAULT 3 COMMENT '客户评分（1-5星）',
    `balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT '客户余额',
    `credit_limit` DECIMAL(15,2) DEFAULT 0.00 COMMENT '信用额度',
    `credit_used` DECIMAL(15,2) DEFAULT 0.00 COMMENT '已用信用额度',
    `level_id` BIGINT DEFAULT NULL COMMENT '客户等级ID',
    `sales_id` BIGINT DEFAULT NULL COMMENT '负责销售ID',
    `sales_name` VARCHAR(100) DEFAULT NULL COMMENT '负责销售姓名',
    `tags` TEXT DEFAULT NULL COMMENT '客户分类标签（JSON格式）',
    `description` TEXT DEFAULT NULL COMMENT '客户简介',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `relationship_level` INT DEFAULT 1 COMMENT '关系级别：1-8级',
    `parent_id` BIGINT DEFAULT NULL COMMENT '父客户ID',
    `group_name` VARCHAR(200) DEFAULT NULL COMMENT '所属集团/连锁名称',
    `is_headquarters` TINYINT(1) DEFAULT TRUE COMMENT '是否为总店',
    `level_path` VARCHAR(500) DEFAULT NULL COMMENT '层级路径',
    `first_deal_at` DATETIME DEFAULT NULL COMMENT '首次成交时间',
    `last_deal_at` DATETIME DEFAULT NULL COMMENT '最近成交时间',
    `last_contact_at` DATETIME DEFAULT NULL COMMENT '最近联系时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `creator_id` BIGINT DEFAULT NULL COMMENT '创建人ID',
    `creator_name` VARCHAR(100) DEFAULT NULL COMMENT '创建人姓名',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_customer_code` (`customer_code`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_level` (`level`),
    KEY `idx_status` (`status`),
    KEY `idx_sales_id` (`sales_id`),
    KEY `idx_parent_id` (`parent_id`),
    KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户表';

-- 客户跟进记录表
CREATE TABLE IF NOT EXISTS `customer_follow_ups` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '跟进记录ID',
    `tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
    `customer_id` BIGINT NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(200) DEFAULT NULL COMMENT '客户名称（冗余）',
    `follow_type` VARCHAR(20) NOT NULL COMMENT '跟进类型：CALL/VISIT/EMAIL/SMS/WECHAT/OTHER',
    `subject` VARCHAR(200) DEFAULT NULL COMMENT '跟进主题',
    `content` TEXT DEFAULT NULL COMMENT '跟进内容',
    `result` VARCHAR(20) DEFAULT NULL COMMENT '跟进结果：SUCCESS/PENDING/FAILED',
    `intention_level` VARCHAR(20) DEFAULT NULL COMMENT '意向等级：HIGH/MEDIUM/LOW',
    `next_follow_up_at` DATETIME DEFAULT NULL COMMENT '下次跟进时间',
    `next_follow_up_plan` TEXT DEFAULT NULL COMMENT '下次跟进计划',
    `attachments` TEXT DEFAULT NULL COMMENT '附件URLs（JSON格式）',
    `status` VARCHAR(20) DEFAULT 'PENDING' COMMENT '跟进状态：DONE/PENDING/CANCELLED',
    `follow_up_user_id` BIGINT DEFAULT NULL COMMENT '跟进人员ID',
    `follow_up_user_name` VARCHAR(100) DEFAULT NULL COMMENT '跟进人员姓名',
    `department` VARCHAR(100) DEFAULT NULL COMMENT '所属部门',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `follow_up_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '跟进时间',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    PRIMARY KEY (`id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_follow_up_user_id` (`follow_up_user_id`),
    KEY `idx_status` (`status`),
    KEY `idx_next_follow_up_at` (`next_follow_up_at`),
    KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户跟进记录表';

-- 客户联系人表
CREATE TABLE IF NOT EXISTS `customer_contacts` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '联系人ID',
    `tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
    `customer_id` BIGINT NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(200) DEFAULT NULL COMMENT '客户名称（冗余）',
    `contact_name` VARCHAR(100) NOT NULL COMMENT '联系人姓名',
    `gender` VARCHAR(10) DEFAULT 'UNKNOWN' COMMENT '性别：MALE/FEMALE/UNKNOWN',
    `department` VARCHAR(100) DEFAULT NULL COMMENT '部门',
    `position` VARCHAR(100) DEFAULT NULL COMMENT '职位',
    `mobile` VARCHAR(50) DEFAULT NULL COMMENT '手机号码',
    `office_phone` VARCHAR(50) DEFAULT NULL COMMENT '办公电话',
    `private_phone` VARCHAR(50) DEFAULT NULL COMMENT '私人电话',
    `email` VARCHAR(100) DEFAULT NULL COMMENT '电子邮箱',
    `backup_email` VARCHAR(100) DEFAULT NULL COMMENT '备用邮箱',
    `wechat` VARCHAR(100) DEFAULT NULL COMMENT '微信',
    `qq` VARCHAR(50) DEFAULT NULL COMMENT 'QQ',
    `other_social` VARCHAR(200) DEFAULT NULL COMMENT '其他社交账号',
    `birthday` VARCHAR(20) DEFAULT NULL COMMENT '生日',
    `anniversary` VARCHAR(20) DEFAULT NULL COMMENT '结婚纪念日',
    `hobbies` TEXT DEFAULT NULL COMMENT '兴趣爱好',
    `contact_type` VARCHAR(50) DEFAULT 'PRIMARY' COMMENT '联系人类型：PRIMARY/SECONDARY/BUSINESS/TECHNICAL/FINANCIAL',
    `is_primary` TINYINT(1) DEFAULT FALSE COMMENT '是否默认联系人',
    `influence_level` VARCHAR(20) DEFAULT 'MEDIUM' COMMENT '决策影响力：HIGH/MEDIUM/LOW',
    `customer_feedback` TEXT DEFAULT NULL COMMENT '客户评价',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `creator_id` BIGINT DEFAULT NULL COMMENT '创建人ID',
    PRIMARY KEY (`id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_mobile` (`mobile`),
    KEY `idx_email` (`email`),
    KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户联系人表';

-- 客户地址表
CREATE TABLE IF NOT EXISTS `customer_addresses` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '地址ID',
    `tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
    `customer_id` BIGINT NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(200) DEFAULT NULL COMMENT '客户名称（冗余）',
    `address_type` VARCHAR(50) DEFAULT 'DELIVERY' COMMENT '地址类型：DELIVERY/INVOICE/BILLING/COMPANY/HOME/OTHER',
    `receiver_name` VARCHAR(100) DEFAULT NULL COMMENT '收货人姓名',
    `receiver_phone` VARCHAR(50) DEFAULT NULL COMMENT '收货人电话',
    `receiver_mobile` VARCHAR(50) DEFAULT NULL COMMENT '收货人手机',
    `province` VARCHAR(50) DEFAULT NULL COMMENT '省',
    `city` VARCHAR(50) DEFAULT NULL COMMENT '市',
    `district` VARCHAR(50) DEFAULT NULL COMMENT '区/县',
    `street` VARCHAR(200) DEFAULT NULL COMMENT '街道/详细地址',
    `full_address` VARCHAR(500) DEFAULT NULL COMMENT '完整地址',
    `postal_code` VARCHAR(20) DEFAULT NULL COMMENT '邮编',
    `longitude` DOUBLE DEFAULT NULL COMMENT '经度',
    `latitude` DOUBLE DEFAULT NULL COMMENT '纬度',
    `map_label` VARCHAR(100) DEFAULT NULL COMMENT '地图标注名称',
    `is_default` TINYINT(1) DEFAULT FALSE COMMENT '是否默认地址',
    `is_delivery_address` TINYINT(1) DEFAULT FALSE COMMENT '是否为发货地址',
    `is_invoice_address` TINYINT(1) DEFAULT FALSE COMMENT '是否为发票地址',
    `business_hours` VARCHAR(200) DEFAULT NULL COMMENT '营业时间',
    `contact_name` VARCHAR(100) DEFAULT NULL COMMENT '联系人姓名',
    `contact_phone` VARCHAR(50) DEFAULT NULL COMMENT '联系人电话',
    `floor_unit` VARCHAR(100) DEFAULT NULL COMMENT '楼层/门牌号',
    `nearby_landmark` VARCHAR(200) DEFAULT NULL COMMENT '附近地标',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `creator_id` BIGINT DEFAULT NULL COMMENT '创建人ID',
    PRIMARY KEY (`id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_address_type` (`address_type`),
    KEY `idx_is_default` (`is_default`),
    KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户地址表';

-- 客户关系表（8级客户关系）
CREATE TABLE IF NOT EXISTS `customer_relationships` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '关系ID',
    `tenant_id` BIGINT DEFAULT NULL COMMENT '租户ID',
    `customer_id` BIGINT NOT NULL COMMENT '客户ID',
    `customer_name` VARCHAR(200) DEFAULT NULL COMMENT '客户名称（冗余）',
    `parent_customer_id` BIGINT DEFAULT NULL COMMENT '父客户ID',
    `parent_customer_name` VARCHAR(200) DEFAULT NULL COMMENT '父客户名称（冗余）',
    `relationship_level` INT DEFAULT 1 COMMENT '关系级别：1-8级',
    `relationship_type` VARCHAR(50) DEFAULT 'BRANCH' COMMENT '关系类型：HEADQUARTERS/BRANCH/AFFILIATE/PARTNER',
    `is_headquarters` TINYINT(1) DEFAULT FALSE COMMENT '是否为总店',
    `group_name` VARCHAR(200) DEFAULT NULL COMMENT '所属集团/连锁名称',
    `group_code` VARCHAR(50) DEFAULT NULL COMMENT '集团编码',
    `level_path` VARCHAR(500) DEFAULT NULL COMMENT '层级路径',
    `depth` INT DEFAULT 1 COMMENT '层级深度',
    `is_direct_relationship` TINYINT(1) DEFAULT TRUE COMMENT '是否直系上下级关系',
    `relationship_desc` TEXT DEFAULT NULL COMMENT '关联关系说明',
    `association_start_at` DATETIME DEFAULT NULL COMMENT '开始关联时间',
    `association_end_at` DATETIME DEFAULT NULL COMMENT '结束关联时间',
    `status` VARCHAR(20) DEFAULT 'ACTIVE' COMMENT '关联状态：ACTIVE/INACTIVE/TERMINATED',
    `remark` TEXT DEFAULT NULL COMMENT '备注',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `deleted_at` DATETIME DEFAULT NULL COMMENT '删除时间',
    `creator_id` BIGINT DEFAULT NULL COMMENT '创建人ID',
    PRIMARY KEY (`id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_parent_customer_id` (`parent_customer_id`),
    KEY `idx_tenant_id` (`tenant_id`),
    KEY `idx_group_name` (`group_name`),
    KEY `idx_relationship_level` (`relationship_level`),
    KEY `idx_status` (`status`),
    KEY `idx_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='客户关系表';
