-- =====================================================================
-- Clinic & Beauty Manager — Migration 001: جداول هسته
-- نیازمندی: MySQL 5.7+ یا MariaDB 10.3+  |  موتور: InnoDB  |  Charset: utf8mb4
-- این فایل idempotent است (چندبار اجرا شود خطا نمی‌دهد).
-- اجرا در phpMyAdmin: انتخاب دیتابیس > Import > انتخاب این فایل > Go
-- نکته: DDL در MySQL «Implicit Commit» دارد و Rollback ندارد؛ به همین دلیل همه
--       CREATE ها با IF NOT EXISTS نوشته شده‌اند تا اجرای دوباره امن باشد.
-- ترتیب: جدولی که Foreign Key می‌گیرد، بعد از جدول مرجع ساخته می‌شود.
-- واحد پول داخلی: ریال (DECIMAL(18,0)) — در جداول مالی فازهای بعد.
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- ثبت نسخه‌های اجراشده
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS schema_migrations (
    version     VARCHAR(20)  NOT NULL,
    description VARCHAR(190) NOT NULL DEFAULT '',
    checksum    CHAR(64)     NULL,
    applied_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- مجموعه (سازمان)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS organizations (
    id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name               VARCHAR(190) NOT NULL,
    business_type      ENUM('clinic','beauty','both') NOT NULL DEFAULT 'both',
    phone              VARCHAR(20)  NULL,
    address            VARCHAR(500) NULL,
    timezone           VARCHAR(64)  NOT NULL DEFAULT 'Asia/Tehran',
    currency_code      VARCHAR(8)   NOT NULL DEFAULT 'IRR',
    money_display_unit ENUM('toman','rial') NOT NULL DEFAULT 'toman',
    is_active          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,
    deleted_at         DATETIME     NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- شعب
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS branches (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    organization_id INT UNSIGNED NOT NULL,
    name            VARCHAR(150) NOT NULL,
    code            VARCHAR(30)  NOT NULL DEFAULT 'main',
    phone           VARCHAR(20)  NULL,
    city            VARCHAR(80)  NULL,
    address         VARCHAR(500) NULL,
    is_default      TINYINT(1)   NOT NULL DEFAULT 0,
    is_active       TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order      INT          NOT NULL DEFAULT 0,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at      DATETIME     NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_branches_org_code (organization_id, code),
    KEY idx_branches_org_active (organization_id, is_active),
    CONSTRAINT fk_branches_org FOREIGN KEY (organization_id) REFERENCES organizations (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- کاربران (حذف فقط منطقی با deleted_at)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    organization_id      INT UNSIGNED NOT NULL,
    username             VARCHAR(60)  NOT NULL,
    mobile               VARCHAR(11)  NULL,
    full_name            VARCHAR(150) NOT NULL,
    email                VARCHAR(190) NULL,
    password_hash        VARCHAR(255) NOT NULL,
    default_branch_id    INT UNSIGNED NULL,
    is_active            TINYINT(1)   NOT NULL DEFAULT 1,
    must_change_password TINYINT(1)   NOT NULL DEFAULT 0,
    password_changed_at  DATETIME     NULL,
    last_login_at        DATETIME     NULL,
    last_login_ip        VARCHAR(45)  NULL,
    created_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at           DATETIME     NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_mobile (mobile),
    KEY idx_users_org_active (organization_id, is_active),
    CONSTRAINT fk_users_org    FOREIGN KEY (organization_id)   REFERENCES organizations (id),
    CONSTRAINT fk_users_branch FOREIGN KEY (default_branch_id) REFERENCES branches (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- نقش‌ها (is_super = دسترسی کامل بدون نیاز به فهرست مجوز)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    organization_id INT UNSIGNED NOT NULL,
    code            VARCHAR(50)  NOT NULL,
    title           VARCHAR(100) NOT NULL,
    description     VARCHAR(255) NULL,
    is_system       TINYINT(1)   NOT NULL DEFAULT 0,
    is_super        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,
    PRIMARY KEY (id),
    UNIQUE KEY uq_roles_org_code (organization_id, code),
    CONSTRAINT fk_roles_org FOREIGN KEY (organization_id) REFERENCES organizations (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- فهرست مجوزها (سراسری؛ با Migration پر می‌شود)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS permissions (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code        VARCHAR(80)  NOT NULL,
    group_code  VARCHAR(40)  NOT NULL,
    group_title VARCHAR(80)  NOT NULL,
    title       VARCHAR(150) NOT NULL,
    sort_order  SMALLINT     NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_permissions_code (code),
    KEY idx_permissions_group (group_code, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
    user_id    INT UNSIGNED NOT NULL,
    role_id    INT UNSIGNED NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    KEY idx_user_roles_role (role_id),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id       INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    KEY idx_role_permissions_perm (permission_id),
    CONSTRAINT fk_role_perm_role FOREIGN KEY (role_id)       REFERENCES roles (id)       ON DELETE CASCADE,
    CONSTRAINT fk_role_perm_perm FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- دسترسی کاربر به شعب
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_branch_access (
    user_id    INT UNSIGNED NOT NULL,
    branch_id  INT UNSIGNED NOT NULL,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, branch_id),
    KEY idx_uba_branch (branch_id),
    CONSTRAINT fk_uba_user   FOREIGN KEY (user_id)   REFERENCES users (id)    ON DELETE CASCADE,
    CONSTRAINT fk_uba_branch FOREIGN KEY (branch_id) REFERENCES branches (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- تنظیمات (branch_id = 0 یعنی تنظیم سطح مجموعه؛ برای جلوگیری از مشکل NULL در UNIQUE)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    organization_id INT UNSIGNED NOT NULL,
    branch_id       INT UNSIGNED NOT NULL DEFAULT 0,
    skey            VARCHAR(100) NOT NULL,
    svalue          TEXT         NULL,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_settings_scope_key (organization_id, branch_id, skey),
    CONSTRAINT fk_settings_org FOREIGN KEY (organization_id) REFERENCES organizations (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Audit Log (عمداً بدون Foreign Key تا سابقه با تغییر/حذف رکوردها باقی بماند؛
--            فقط درج، هیچ‌گاه ویرایش یا حذف از برنامه)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_logs (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    organization_id INT UNSIGNED NULL,
    branch_id       INT UNSIGNED NULL,
    user_id         INT UNSIGNED NULL,
    action          VARCHAR(80)  NOT NULL,
    entity_type     VARCHAR(60)  NULL,
    entity_id       VARCHAR(40)  NULL,
    description     VARCHAR(500) NOT NULL DEFAULT '',
    meta            TEXT         NULL,
    ip              VARCHAR(45)  NULL,
    user_agent      VARCHAR(255) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_org_time (organization_id, created_at),
    KEY idx_audit_user_time (user_id, created_at),
    KEY idx_audit_action_time (action, created_at),
    KEY idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- تلاش‌های ورود (برای محدودیت تلاش ناموفق در فاز ۲)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_attempts (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    identifier VARCHAR(100) NOT NULL,
    ip         VARCHAR(45)  NOT NULL,
    success    TINYINT(1)   NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_login_identifier (identifier, created_at),
    KEY idx_login_ip (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- گزارش اجرای Cron
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cron_runs (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job         VARCHAR(60)  NOT NULL,
    status      ENUM('running','success','failed','skipped') NOT NULL DEFAULT 'running',
    started_at  DATETIME     NOT NULL,
    finished_at DATETIME     NULL,
    duration_ms INT UNSIGNED NULL,
    message     VARCHAR(500) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    KEY idx_cron_job_time (job, started_at),
    KEY idx_cron_status (status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- ثبت این Migration (برای زمانی که فایل را دستی در phpMyAdmin اجرا می‌کنید)
-- ---------------------------------------------------------------------
INSERT IGNORE INTO schema_migrations (version, description) VALUES ('001', 'core_tables');