-- 人员信息表
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_id       VARCHAR(50)                 COMMENT '职业ID',
    village_id          INT                         COMMENT '所属村庄ID',
    grid_id             INT                         COMMENT '所属网格ID',
    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 '户籍关系',
    current_address     VARCHAR(200)                COMMENT '现居地',
    special_identity_code VARCHAR(20)               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_grid (grid_id),
    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-非农业户口',
    village_id              INT                             COMMENT '所属行政村ID',
    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 '删除标志'
    INDEX idx_household_number (household_code),
    INDEX idx_household_head (household_head),
    INDEX idx_house_number (house_number)
) 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)',
    village_id        INT                   COMMENT '所属行政村ID',
    village_name      VARCHAR(100)          COMMENT '所属行政村名称',
    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_type        TINYINT               COMMENT '房屋性质:1-自建房,2-商铺,3-公租房',
    house_category    TINYINT               COMMENT '房屋类别:1-修缮,2-原址重建,3-集中安置',
    building_area     DECIMAL(10,2)         COMMENT '宅基地面积(㎡)',
    grid_id           INT                   COMMENT '所属网格ID',
    photo_url         VARCHAR(256)          COMMENT '照片URL',
    house_tags        VARCHAR(1024)         COMMENT '家庭标签(JSON数组)',
    qr_code_url       VARCHAR(256)          COMMENT '门牌二维码图片URL',
    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_village_grid (village_id, grid_id),
    INDEX idx_doorplate (doorplate_number),
    INDEX idx_owner (house_owner_id),
    INDEX idx_house_type (house_type),
    INDEX idx_house_category (house_category),
    INDEX idx_address (house_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='房屋信息表';

-- 网格信息表
CREATE TABLE grids (
    grid_id INT PRIMARY KEY AUTO_INCREMENT,
    grid_code VARCHAR(20) UNIQUE NOT NULL COMMENT '网格编码',
    grid_name VARCHAR(100) NOT NULL COMMENT '网格名称',
    grid_level TINYINT NOT NULL COMMENT '网格级别(1-一级,2-二级,3-三级...)',
    parent_id INT COMMENT '上级网格ID',
    full_path VARCHAR(255) COMMENT '完整路径(格式:1,12,123)',
    village_id INT COMMENT '所属行政村ID',
    manager_id INT COMMENT '网格管理员ID',
    manager_name VARCHAR(50) COMMENT '网格管理员姓名',
    manager_phone VARCHAR(20) COMMENT '管理员电话',
    area_size DECIMAL(10,2) COMMENT '面积(平方公里)',
    household_count INT DEFAULT 0 COMMENT '户数',
    population_count INT DEFAULT 0 COMMENT '人口数',
    description VARCHAR(200) COMMENT '描述',
    status TINYINT DEFAULT 1 COMMENT '状态:1-启用,0-停用',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES grids(grid_id),
    FOREIGN KEY (village_id) REFERENCES villages(village_id),
    INDEX idx_level (grid_level),
    INDEX idx_parent (parent_id),
    INDEX idx_village (village_id),
    INDEX idx_path (full_path(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 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='项目表';
