-- IAM 表结构（与 internal/iam/entity 中 GORM 模型一致；PostgreSQL）
-- 执行：psql $DATABASE_URL -f migrations/postgres/001_iam.sql

BEGIN;

-- 租户
CREATE TABLE IF NOT EXISTS iam_tenant (
    id              varchar(36) PRIMARY KEY,
    tenant_code     varchar(64)  NOT NULL,
    tenant_name     varchar(128) NOT NULL,
    admin_user_id   varchar(36)  NULL,
    status          smallint     NOT NULL DEFAULT 1,
    expire_time     timestamptz  NULL,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now(),
    deleted_at      timestamptz  NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_tenant_code ON iam_tenant (tenant_code);
CREATE INDEX IF NOT EXISTS idx_iam_tenant_deleted_at ON iam_tenant (deleted_at);

-- 部门（根部门 parent_id 为空串）
CREATE TABLE IF NOT EXISTS iam_dept (
    id          varchar(36) PRIMARY KEY,
    tenant_id   varchar(36)  NOT NULL,
    parent_id   varchar(36)  NOT NULL DEFAULT '',
    dept_name   varchar(128) NOT NULL,
    dept_path   text         NULL,
    leader_id   varchar(36)  NULL,
    sort_order  int          NOT NULL DEFAULT 0,
    status      smallint     NOT NULL DEFAULT 1,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    deleted_at  timestamptz  NULL
);
CREATE INDEX IF NOT EXISTS idx_dept_tenant ON iam_dept (tenant_id);
CREATE INDEX IF NOT EXISTS idx_dept_parent ON iam_dept (parent_id);
CREATE INDEX IF NOT EXISTS idx_iam_dept_deleted_at ON iam_dept (deleted_at);

-- 用户
CREATE TABLE IF NOT EXISTS iam_user (
    id             varchar(36) PRIMARY KEY,
    tenant_id      varchar(36)  NOT NULL,
    dept_id          varchar(36)  NULL,
    user_name      varchar(64)  NOT NULL,
    real_name      varchar(64)  NULL,
    password_hash  varchar(255) NOT NULL,
    phone          varchar(20)  NULL,
    email          varchar(128) NULL,
    avatar         varchar(512) NULL,
    gender         smallint     NOT NULL DEFAULT 0,
    status         smallint     NOT NULL DEFAULT 1,
    login_attempts int          NOT NULL DEFAULT 0,
    locked_until   timestamptz  NULL,
    last_login_at  timestamptz  NULL,
    last_login_ip  varchar(45)  NULL,
    created_at     timestamptz  NOT NULL DEFAULT now(),
    updated_at     timestamptz  NOT NULL DEFAULT now(),
    deleted_at     timestamptz  NULL
);
CREATE INDEX IF NOT EXISTS idx_user_tenant ON iam_user (tenant_id);
CREATE INDEX IF NOT EXISTS idx_user_dept ON iam_user (dept_id);
CREATE INDEX IF NOT EXISTS idx_iam_user_deleted_at ON iam_user (deleted_at);

-- 用户-部门关联
CREATE TABLE IF NOT EXISTS iam_user_dept (
    id         varchar(36) PRIMARY KEY,
    user_id    varchar(36) NOT NULL,
    dept_id    varchar(36) NOT NULL,
    is_primary boolean     NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uk_user_dept UNIQUE (user_id, dept_id)
);

-- 角色
CREATE TABLE IF NOT EXISTS iam_role (
    id          varchar(36) PRIMARY KEY,
    tenant_id   varchar(36)  NOT NULL,
    role_code   varchar(64)  NOT NULL,
    role_name   varchar(128) NOT NULL,
    data_scope  smallint       NOT NULL DEFAULT 4,
    description varchar(512) NULL,
    is_builtin  boolean        NOT NULL DEFAULT false,
    status      smallint       NOT NULL DEFAULT 1,
    created_at  timestamptz    NOT NULL DEFAULT now(),
    updated_at  timestamptz    NOT NULL DEFAULT now(),
    deleted_at  timestamptz    NULL
);
CREATE INDEX IF NOT EXISTS idx_role_tenant ON iam_role (tenant_id);
CREATE INDEX IF NOT EXISTS idx_iam_role_deleted_at ON iam_role (deleted_at);

-- 菜单（全局）
CREATE TABLE IF NOT EXISTS iam_menu (
    id            varchar(36) PRIMARY KEY,
    parent_id     varchar(36)  NOT NULL DEFAULT '',
    menu_name     varchar(128) NOT NULL,
    menu_type     smallint     NOT NULL,
    perms         varchar(128) NULL,
    path          varchar(255) NULL,
    component     varchar(255) NULL,
    icon          varchar(64)  NULL,
    sort_order    int          NOT NULL DEFAULT 0,
    is_visible    boolean      NOT NULL DEFAULT true,
    is_builtin    boolean      NOT NULL DEFAULT false,
    external_link varchar(512) NULL,
    status        smallint     NOT NULL DEFAULT 1,
    created_at    timestamptz  NOT NULL DEFAULT now(),
    updated_at    timestamptz  NOT NULL DEFAULT now(),
    deleted_at    timestamptz  NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_iam_menu_perms ON iam_menu (perms);
CREATE INDEX IF NOT EXISTS idx_menu_parent ON iam_menu (parent_id);
CREATE INDEX IF NOT EXISTS idx_iam_menu_deleted_at ON iam_menu (deleted_at);

-- 用户-角色
CREATE TABLE IF NOT EXISTS iam_user_role (
    id         varchar(36) PRIMARY KEY,
    user_id    varchar(36) NOT NULL,
    role_id    varchar(36) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uk_user_role UNIQUE (user_id, role_id)
);

-- 角色-菜单
CREATE TABLE IF NOT EXISTS iam_role_menu (
    id         varchar(36) PRIMARY KEY,
    role_id    varchar(36) NOT NULL,
    menu_id    varchar(36) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uk_role_menu UNIQUE (role_id, menu_id)
);

COMMIT;