-- CommerceInsight Phase 1 permissions schema
-- Target database: shujuku2_system
-- Purpose:
--   1. Board-level permissions
--   2. Board-level data scopes
--   3. Stable user-to-owner bindings
--   4. Optional field-level permissions for V1.5

SET NAMES utf8mb4;

USE shujuku2_system;

CREATE TABLE IF NOT EXISTS board_permissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    page_key VARCHAR(100) NOT NULL,
    board_key VARCHAR(100) NOT NULL,
    subject_type ENUM('role', 'department', 'user') NOT NULL,
    subject_id VARCHAR(100) NOT NULL,
    allowed TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_board_permissions_subject (page_key, board_key, subject_type, subject_id),
    KEY idx_board_permissions_board (page_key, board_key),
    KEY idx_board_permissions_subject (subject_type, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Board-level permissions';

CREATE TABLE IF NOT EXISTS board_data_scopes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    page_key VARCHAR(100) NOT NULL,
    board_key VARCHAR(100) NOT NULL,
    subject_type ENUM('role', 'department', 'user') NOT NULL,
    subject_id VARCHAR(100) NOT NULL,
    scope_type ENUM('all', 'self', 'department', 'self_and_department', 'custom_departments') NOT NULL DEFAULT 'department',
    scope_value_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_board_data_scopes_subject (page_key, board_key, subject_type, subject_id),
    KEY idx_board_data_scopes_board (page_key, board_key),
    KEY idx_board_data_scopes_subject (subject_type, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Board-level data scopes';

CREATE TABLE IF NOT EXISTS user_owner_bindings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    binding_type ENUM('owner', 'oper', 'dept') NOT NULL,
    binding_value VARCHAR(100) NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_owner_binding (user_id, binding_type, binding_value),
    KEY idx_user_owner_bindings_user (user_id),
    KEY idx_user_owner_bindings_lookup (binding_type, binding_value)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stable mapping between system users and business owners';

CREATE TABLE IF NOT EXISTS board_field_permissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    page_key VARCHAR(100) NOT NULL,
    board_key VARCHAR(100) NOT NULL,
    field_key VARCHAR(100) NOT NULL,
    subject_type ENUM('role', 'department', 'user') NOT NULL,
    subject_id VARCHAR(100) NOT NULL,
    visible TINYINT(1) NOT NULL DEFAULT 1,
    exportable TINYINT(1) NOT NULL DEFAULT 0,
    mask_rule VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_board_field_permissions_subject (page_key, board_key, field_key, subject_type, subject_id),
    KEY idx_board_field_permissions_board (page_key, board_key),
    KEY idx_board_field_permissions_field (field_key),
    KEY idx_board_field_permissions_subject (subject_type, subject_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Field-level permissions for V1.5';

-- Optional bootstrap examples:
-- INSERT INTO board_permissions (page_key, board_key, subject_type, subject_id, allowed)
-- VALUES ('tmall', 'overview', 'role', 'admin', 1);
--
-- INSERT INTO board_data_scopes (page_key, board_key, subject_type, subject_id, scope_type, scope_value_json)
-- VALUES ('tmall', 'overview', 'department', '12', 'custom_departments', JSON_ARRAY('Sales', 'E-Commerce'));