-- 人员信息表
CREATE TABLE person_info (
    id                  INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    realname            VARCHAR(50) NOT NULL        COMMENT '姓名',
    id_card             VARCHAR(18) UNIQUE          COMMENT '身份证号',
    ethnic              VARCHAR(30)                 COMMENT '民族',
    age                 TINYINT                     COMMENT '年龄',
    gender              TINYINT NOT NULL            COMMENT '性别: 1-男, 2-女, 0-未知',
    birth_date          DATE                        COMMENT '出生日期',
    population_category TINYINT                     COMMENT '人口类别：0-本村户籍 ，1-非本村户籍',
    phone               VARCHAR(20)                 COMMENT '电话',
    population_tags     VARCHAR(1024)               COMMENT '人口标签(标签 json 列表)',
    occupation          VARCHAR(50)                 COMMENT '职业',
    grid                VARCHAR(30)                 COMMENT '所属网格',
    education_level     VARCHAR(20)                 COMMENT '文化程度',
    marital_status      TINYINT                     COMMENT '婚姻状况: 1-未婚, 2-已婚, 3-丧偶, 4-离婚',
    political_status    VARCHAR(20)                 COMMENT '政治面貌',
    religion            VARCHAR(30)                 COMMENT '宗教信仰',
    photo_url           VARCHAR(255)                COMMENT '照片URL',
    household_id        INT                         COMMENT '所属户籍ID',
    household_relation  VARCHAR(30)                 COMMENT '户籍关系',
    special_identity    VARCHAR(100)                COMMENT '特殊身份',
    create_time         datetime         null comment '创建时间',
    create_by           varchar(64)      null comment '创建人',
    update_time         datetime         null comment '修改时间',
    update_by           varchar(64)      null comment '修改人',
    del_flag            char default '0' null comment '删除标志',
    INDEX idx_realname (realname),
    INDEX idx_ethnic (ethnic),
    INDEX idx_id_card (id_card),
    INDEX idx_household (household_id),
    INDEX idx_special_identity (special_identity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员信息表';

-- 户籍信息表
CREATE TABLE household_info (
    id                    INT PRIMARY KEY AUTO_INCREMENT    COMMENT '户籍ID',
    household_code        VARCHAR(50) UNIQUE  NOT NULL      COMMENT '户籍编号',
    household_head          VARCHAR(50) NOT NULL            COMMENT '户主姓名',
    household_head_id_card  VARCHAR(18)                     COMMENT '户主身份证号',
    household_type          TINYINT     NOT NULL            COMMENT '户籍类型: 1-农业户口, 2-非农业户口',
    household_address       VARCHAR(200)                    COMMENT '户籍地址',
    belonging_area          VARCHAR(200)                    COMMENT '归属地区',
    house_code            VARCHAR(50)                       COMMENT '房屋编号',
    create_time         datetime         null comment '创建时间',
    update_time         datetime         null comment '修改时间',
    del_flag            char default '0' null comment '删除标志',
    del_flag        char default '0' null comment '删除标志',
    INDEX idx_household_number (household_code),
    INDEX idx_household_head (household_head),
    INDEX idx_house_code (house_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='户籍信息表';

-- 房屋信息表
CREATE TABLE house_info (
    id          INT    PRIMARY KEY  AUTO_INCREMENT COMMENT '房屋ID',
    house_code     VARCHAR(20) UNIQUE NOT NULL COMMENT '房屋编号(如FW0000580689)',
    doorplate_number  VARCHAR(50)           COMMENT '门牌号',
    house_address     VARCHAR(200)          COMMENT '房屋详细地址',
    house_owner_id    INT                   COMMENT '房主人员ID',
    total_points      INT DEFAULT 0         COMMENT '总积分',
    remaining_points  INT DEFAULT 0         COMMENT '剩余积分',
    house_category    TINYINT               COMMENT '房屋类别:1-修缮,2-原址重建,3-集中安置',
    building_area     DECIMAL(10,2)         COMMENT '宅基地面积(㎡)',
    grid             INT                    COMMENT '所属网格',
    photo_url         VARCHAR(256)          COMMENT '照片URL',
    house_tags        VARCHAR(1024)         COMMENT '家庭标签(JSON数组)',
    create_time       datetime         null comment '创建时间',
    update_time       datetime         null comment '修改时间',
    del_flag          char default '0' null comment '删除标志',
    -- 索引
    INDEX idx_house_code (house_code),
    INDEX idx_doorplate (doorplate_number),
    INDEX idx_owner (house_owner_id),
    INDEX idx_house_category (house_category),
    INDEX idx_address (house_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房屋信息表';

-- 人员-房屋 关联表
CREATE TABLE person_house_relation (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',
    person_id INT NOT NULL COMMENT '人员ID',
    house_id INT NOT NULL COMMENT '房屋ID',
    create_time       datetime         null comment '创建时间',
    update_time       datetime         null comment '修改时间',
    del_flag          char default '0' null comment '删除标志',
    -- 联合唯一约束（防止重复关联）
    UNIQUE KEY uk_person_house (person_id, house_id)
) ENGINE=InnoDB COMMENT='人员房屋关系表';

-- 项目表
CREATE TABLE projects (
    id                  INT PRIMARY KEY AUTO_INCREMENT COMMENT '项目ID',
    project_name        VARCHAR(100) NOT NULL   COMMENT '项目名称',
    project_description VARCHAR(256)            COMMENT '项目内容',
    investment_amount   DECIMAL(15,2)           COMMENT '投资金额(元)',
    start_date          DATE                    COMMENT '项目开始日期',
    estimated_completion_date   DATE            COMMENT '预计建成日期',
    actual_completion_date      DATE            COMMENT '实际建成日期',
    progress                    TINYINT DEFAULT 0 COMMENT '项目进度(0-建设中 1-已建成)',
    create_time     datetime         null       comment '创建时间',
    update_time     datetime         null       comment '修改时间',
    del_flag          char default '0' null comment '删除标志',
    INDEX idx_progress (progress),
    INDEX idx_name (project_name),
    INDEX idx_dates (start_date, estimated_completion_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目表';

-- 补贴（项目管理）
CREATE TABLE subsidy_projects (
    id  INT PRIMARY KEY AUTO_INCREMENT                  COMMENT '序号ID',
    project_name VARCHAR(100) NOT NULL                  COMMENT '项目名称',
    subsidy_type VARCHAR(100) NOT NULL                  COMMENT '补贴类别',
    realname            VARCHAR(50) NOT NULL            COMMENT '姓名',
    id_card        VARCHAR(18) UNIQUE                   COMMENT '身份证号',
    subsidy_amount DECIMAL(20,2)                        COMMENT '实发金额(元)',
    bank_account_hash CHAR(64) NOT NULL                 COMMENT '银行账号SHA-256哈希（加密存储）',
    payment_date DATE NOT NULL                          COMMENT '兑现时间',
    status VARCHAR(20)      NOT NULL                    COMMENT '状态',
    create_time     datetime         null               comment '创建时间',
    update_time     datetime         null               comment '修改时间',
    del_flag        char default '0' null               comment '删除标志',
    INDEX idx_type (subsidy_type),
    INDEX idx_name (realname),
    INDEX idx_payment_date (payment_date),
    INDEX idx_status (status),
    INDEX idx_type_status (subsidy_type, status),
    INDEX idx_type_name_date (subsidy_type, realname, payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='补贴项目管理表';