-- ============================================================
-- Assets Management System 1.0
-- सम्पत्ति व्यवस्थापन प्रणाली १.०
-- Phase 1: Foundation Schema
-- ============================================================
-- Engine/charset per spec section 95.
-- This file is idempotent-safe for a fresh install only.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- organizations
-- Top-level institution record (single-institution deployment
-- for now; org_units for departments/locations arrive in Phase 2).
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS organizations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    name_np VARCHAR(255) NULL,
    institution_type VARCHAR(100) NULL COMMENT 'school, local government office, community org, etc.',
    province VARCHAR(100) NULL,
    district VARCHAR(100) NULL,
    local_level VARCHAR(100) NULL,
    ward VARCHAR(20) NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    pan_number VARCHAR(50) NULL,
    registration_info VARCHAR(255) NULL,
    logo_path VARCHAR(255) NULL,
    timezone VARCHAR(50) NOT NULL DEFAULT 'Asia/Kathmandu',
    currency_code VARCHAR(10) NOT NULL DEFAULT 'NPR',
    date_format VARCHAR(20) NOT NULL DEFAULT 'YYYY-MM-DD',
    default_language VARCHAR(5) NOT NULL DEFAULT 'en',
    asset_number_prefix VARCHAR(20) NOT NULL DEFAULT 'AST',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- fiscal_years
-- Nepali fiscal year as a first-class entity (spec section 40).
-- Never derive fiscal year from server date alone.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS fiscal_years (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    code VARCHAR(20) NOT NULL COMMENT 'e.g. 2082/83',
    start_date_ad DATE NOT NULL,
    end_date_ad DATE NOT NULL,
    start_date_bs VARCHAR(15) NULL COMMENT 'e.g. 2082-04-01',
    end_date_bs VARCHAR(15) NULL,
    is_current TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('upcoming','active','closed') NOT NULL DEFAULT 'upcoming',
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_fiscal_year_code (organization_id, code),
    CONSTRAINT fk_fy_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- roles / permissions / role_permissions
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) NOT NULL UNIQUE COMMENT 'machine key, e.g. super_admin',
    name VARCHAR(100) NOT NULL,
    name_np VARCHAR(100) NULL,
    description VARCHAR(255) NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'system roles cannot be deleted',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE COMMENT 'e.g. assets.approve',
    module VARCHAR(50) NOT NULL COMMENT 'e.g. assets, procurement, inventory',
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) 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),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- users / user_roles
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(60) NOT NULL UNIQUE,
    email VARCHAR(150) NULL UNIQUE,
    mobile VARCHAR(30) NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','inactive','locked') NOT NULL DEFAULT 'active',
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    last_login_ip VARCHAR(64) NULL,
    failed_login_count INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    password_reset_token VARCHAR(100) NULL,
    password_reset_expires DATETIME NULL,
    deleted_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) 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,
    assigned_by INT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- login_attempts — brute-force / audit tracking
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(150) NOT NULL,
    ip_address VARCHAR(64) NOT NULL,
    user_agent VARCHAR(255) NULL,
    success TINYINT(1) NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_attempts_username (username),
    INDEX idx_login_attempts_ip (ip_address)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- system_settings — key/value config, never hard-coded (section 2, 85)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS system_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_setting (organization_id, setting_key),
    CONSTRAINT fk_settings_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- numbering_sequences — concurrency-safe document numbering (section 63)
-- Never use SELECT MAX(number)+1. last_number is incremented inside
-- a single UPDATE ... then read back, guarded by a transaction.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS numbering_sequences (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    sequence_key VARCHAR(50) NOT NULL COMMENT 'e.g. AST, GRN, TRF, MNT',
    fiscal_year_id INT UNSIGNED NULL,
    last_number BIGINT UNSIGNED NOT NULL DEFAULT 0,
    padding TINYINT UNSIGNED NOT NULL DEFAULT 6,
    UNIQUE KEY uq_sequence (organization_id, sequence_key, fiscal_year_id),
    CONSTRAINT fk_seq_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_seq_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- audit_logs — append-only. No UPDATE/DELETE grants for app role.
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NULL,
    user_id INT UNSIGNED NULL,
    username_snapshot VARCHAR(150) NULL COMMENT 'preserved even if user is later removed',
    action VARCHAR(50) NOT NULL COMMENT 'create, update, approve, reject, login, logout, export, ...',
    module VARCHAR(50) NOT NULL,
    record_id VARCHAR(50) NULL,
    old_data JSON NULL,
    new_data JSON NULL,
    reason VARCHAR(255) NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_module (module),
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;