-- ============================================================
-- V3__user_extension.sql
-- 用户权限体系扩展脚本
-- 创建部门表、企业员工扩展表、项目员工扩展表、业主扩展表、房屋表、业主房屋绑定表
-- 同时扩展 auth_user 和 auth_role 表
-- ============================================================

BEGIN;

-- ============================================================
-- 1. 扩展 auth_user 表 - 添加用户类型和部门字段
-- ============================================================

ALTER TABLE auth_user ADD COLUMN IF NOT EXISTS user_type VARCHAR(20) DEFAULT 'ENTERPRISE';
ALTER TABLE auth_user ADD COLUMN IF NOT EXISTS dept_id UUID;

-- ============================================================
-- 2. 创建部门表
-- ============================================================

CREATE TABLE IF NOT EXISTS dept (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id UUID REFERENCES dept(id) ON DELETE SET NULL,
    dept_name VARCHAR(100) NOT NULL,
    dept_code VARCHAR(50) UNIQUE,
    leader_id UUID REFERENCES auth_user(id) ON DELETE SET NULL,
    sort_order INT DEFAULT 0,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_dept_parent ON dept(parent_id);

-- ============================================================
-- 3. 创建企业员工扩展表
-- ============================================================

CREATE TABLE IF NOT EXISTS enterprise_user (
    user_id UUID PRIMARY KEY REFERENCES auth_user(id) ON DELETE CASCADE,
    employee_no VARCHAR(50) UNIQUE,
    dept_id UUID REFERENCES dept(id) ON DELETE SET NULL,
    position VARCHAR(100),
    entry_date DATE,
    user_category VARCHAR(20) DEFAULT 'ENTERPRISE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_enterprise_user_dept ON enterprise_user(dept_id);

-- ============================================================
-- 4. 创建项目员工扩展表
-- ============================================================

CREATE TABLE IF NOT EXISTS project_staff (
    user_id UUID PRIMARY KEY REFERENCES auth_user(id) ON DELETE CASCADE,
    project_id UUID NOT NULL,
    staff_type VARCHAR(20) DEFAULT 'GENERAL',
    shift_type VARCHAR(20) DEFAULT 'DAY',
    leader_id UUID REFERENCES auth_user(id) ON DELETE SET NULL,
    assignment_status VARCHAR(20) DEFAULT 'ASSIGNED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_project_staff_project ON project_staff(project_id);
CREATE INDEX IF NOT EXISTS idx_project_staff_user ON project_staff(user_id);

-- ============================================================
-- 5. 创建业主扩展表
-- ============================================================

CREATE TABLE IF NOT EXISTS resident (
    user_id UUID PRIMARY KEY REFERENCES auth_user(id) ON DELETE CASCADE,
    id_card VARCHAR(18),
    resident_type VARCHAR(20) DEFAULT 'OWNER',
    verification_status VARCHAR(20) DEFAULT 'UNVERIFIED',
    verified_at TIMESTAMP,
    verified_by UUID REFERENCES auth_user(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_resident_type ON resident(resident_type);

-- ============================================================
-- 6. 创建房屋表
-- ============================================================

CREATE TABLE IF NOT EXISTS space (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    building VARCHAR(50) NOT NULL,
    unit VARCHAR(20) NOT NULL,
    room_no VARCHAR(20) NOT NULL,
    space_type VARCHAR(20) DEFAULT 'RESIDENTIAL',
    floor INT,
    unit_area DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(project_id, building, unit, room_no)
);

CREATE INDEX IF NOT EXISTS idx_space_project ON space(project_id);

-- ============================================================
-- 7. 创建业主房屋绑定表
-- ============================================================

CREATE TABLE IF NOT EXISTS resident_space (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth_user(id) ON DELETE CASCADE,
    space_id UUID NOT NULL REFERENCES space(id) ON DELETE CASCADE,
    relation_type VARCHAR(20) DEFAULT 'OWNER',
    binding_status VARCHAR(20) DEFAULT 'ACTIVE',
    start_date DATE,
    end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_resident_space_user ON resident_space(user_id);
CREATE INDEX IF NOT EXISTS idx_resident_space_space ON resident_space(space_id);

-- ============================================================
-- 8. 扩展 auth_user_role 表 - 支持项目级角色
-- ============================================================

ALTER TABLE auth_user_role ADD COLUMN IF NOT EXISTS project_id UUID;
ALTER TABLE auth_user_role ADD COLUMN IF NOT EXISTS scope VARCHAR(20) DEFAULT 'ENTERPRISE';

CREATE INDEX IF NOT EXISTS idx_user_role_scope ON auth_user_role(project_id, scope);

-- ============================================================
-- 9. 更新 auth_role 表 - 增强角色类型
-- ============================================================

ALTER TABLE auth_role ADD COLUMN IF NOT EXISTS scope VARCHAR(20) DEFAULT 'ENTERPRISE';
ALTER TABLE auth_role ADD COLUMN IF NOT EXISTS project_id UUID;

CREATE INDEX IF NOT EXISTS idx_role_scope ON auth_role(scope);

-- ============================================================
-- 10. 更新现有数据
-- ============================================================

UPDATE auth_user SET user_type = 'ENTERPRISE' WHERE user_type IS NULL;
UPDATE auth_role SET type = 'SYSTEM', scope = 'ENTERPRISE' WHERE type IS NULL;

COMMIT;

-- ============================================================
-- 添加表和列注释（单独执行）
-- ============================================================

COMMENT ON TABLE dept IS '部门表';
COMMENT ON COLUMN dept.parent_id IS '上级部门ID';
COMMENT ON COLUMN dept.dept_name IS '部门名称';
COMMENT ON COLUMN dept.dept_code IS '部门编码';
COMMENT ON COLUMN dept.leader_id IS '部门负责人ID';

COMMENT ON TABLE enterprise_user IS '企业员工扩展表';
COMMENT ON COLUMN enterprise_user.employee_no IS '员工工号';
COMMENT ON COLUMN enterprise_user.dept_id IS '所属部门';
COMMENT ON COLUMN enterprise_user.position IS '职位';
COMMENT ON COLUMN enterprise_user.entry_date IS '入职日期';
COMMENT ON COLUMN enterprise_user.user_category IS '员工类别: ENTERPRISE-职能员工, MANAGEMENT-管理岗';

COMMENT ON TABLE project_staff IS '项目员工扩展表';
COMMENT ON COLUMN project_staff.project_id IS '所属项目';
COMMENT ON COLUMN project_staff.staff_type IS '员工类型: SECURITY-保安, CLEANING-保洁, GARDEN-绿化, MAINTENANCE-维修, CUSTOMER_SERVICE-客服, GENERAL-普通';
COMMENT ON COLUMN project_staff.shift_type IS '班次类型: DAY-白班, NIGHT-夜班, ROTATION-轮班';
COMMENT ON COLUMN project_staff.leader_id IS '班组长ID';
COMMENT ON COLUMN project_staff.assignment_status IS '在岗状态: ASSIGNED-在岗, ON_LEAVE-休假, TRANSFERRED-调岗';

COMMENT ON TABLE resident IS '业主住户扩展表';
COMMENT ON COLUMN resident.id_card IS '身份证号';
COMMENT ON COLUMN resident.resident_type IS '住户类型: OWNER-业主, FAMILY-家庭成员, TENANT-租户';
COMMENT ON COLUMN resident.verification_status IS '认证状态: UNVERIFIED-未认证, PENDING-待审核, VERIFIED-已认证, REJECTED-已拒绝';
COMMENT ON COLUMN resident.verified_by IS '认证人ID';

COMMENT ON TABLE space IS '房屋表';
COMMENT ON COLUMN space.project_id IS '所属项目';
COMMENT ON COLUMN space.building IS '楼栋';
COMMENT ON COLUMN space.unit IS '单元';
COMMENT ON COLUMN space.room_no IS '房号';
COMMENT ON COLUMN space.space_type IS '房屋类型: RESIDENTIAL-住宅, COMMERCIAL-商办';
COMMENT ON COLUMN space.floor IS '楼层';
COMMENT ON COLUMN space.unit_area IS '建筑面积';

COMMENT ON TABLE resident_space IS '业主房屋绑定表';
COMMENT ON COLUMN resident_space.user_id IS '用户ID';
COMMENT ON COLUMN resident_space.space_id IS '房屋ID';
COMMENT ON COLUMN resident_space.relation_type IS '关系类型: OWNER-业主, FAMILY-家属, TENANT-租户';
COMMENT ON COLUMN resident_space.binding_status IS '绑定状态: PENDING-待生效, ACTIVE-生效, EXPIRED-已过期, CANCELLED-已解绑';
COMMENT ON COLUMN resident_space.start_date IS '绑定开始日期';
COMMENT ON COLUMN resident_space.end_date IS '绑定结束日期';

COMMENT ON COLUMN auth_user.user_type IS '用户类型: ENTERPRISE-企业员工, PROJECT_STAFF-项目员工, RESIDENT-业主, CUSTOMER-客户';
COMMENT ON COLUMN auth_user.dept_id IS '所属部门ID';
COMMENT ON COLUMN auth_user_role.project_id IS '项目级角色的所属项目';
COMMENT ON COLUMN auth_user_role.scope IS '角色生效范围: ENTERPRISE-企业级, PROJECT-项目级, RESIDENT-住户级';
COMMENT ON COLUMN auth_role.scope IS '角色生效范围: ENTERPRISE-企业级, PROJECT-项目级';
COMMENT ON COLUMN auth_role.project_id IS '项目级角色的所属项目';