-- DemoPay — schema (DEMO ONLY, all data is fake).
--
-- Run via `php setup.php`, which creates the database, executes this file, and
-- seeds fake data. You may also import it manually into an existing database.
--
-- Production would manage this with a real migration tool and add: encryption
-- at rest for PII, a double-entry ledger for money movement, and audit tables.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(120)  NOT NULL,
    email            VARCHAR(190)  NOT NULL UNIQUE,
    password_hash    VARCHAR(255)  NOT NULL,           -- bcrypt via password_hash()
    phone            VARCHAR(40)   DEFAULT NULL,
    profile_photo    VARCHAR(255)  DEFAULT NULL,       -- filename in public/uploads/demo/
    account_approved TINYINT(1)    NOT NULL DEFAULT 0, -- toggled by admin
    kyc_status       ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounts (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    user_id        INT           NOT NULL,
    account_number VARCHAR(20)   NOT NULL UNIQUE,
    account_type   ENUM('savings','checking') NOT NULL DEFAULT 'checking',
    balance        DECIMAL(14,2) NOT NULL DEFAULT 0.00,  -- FAKE balance
    currency       VARCHAR(3)    NOT NULL DEFAULT 'USD',
    status         ENUM('active','frozen','closed') NOT NULL DEFAULT 'active',
    created_at     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    account_id    INT           NOT NULL,
    type          ENUM('credit','debit') NOT NULL,
    amount        DECIMAL(14,2) NOT NULL,
    description   VARCHAR(255)  NOT NULL DEFAULT '',
    counterparty  VARCHAR(190)  NOT NULL DEFAULT '',
    balance_after DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    reference     VARCHAR(40)   NOT NULL,
    status        ENUM('completed','pending','failed') NOT NULL DEFAULT 'completed',
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_txn_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_txn_account_date (account_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kyc_docs (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_id       INT           NOT NULL,
    doc_type      ENUM('passport','national_id','drivers_license','utility_bill') NOT NULL,
    file_path     VARCHAR(255)  NOT NULL,   -- filename in public/uploads/kyc_demo/
    status        ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewer_note VARCHAR(255)  NOT NULL DEFAULT '',
    uploaded_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reviewed_at   DATETIME      DEFAULT NULL,
    CONSTRAINT fk_kyc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_tickets (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT           DEFAULT NULL,
    name       VARCHAR(120)  NOT NULL,
    email      VARCHAR(190)  NOT NULL,
    subject    VARCHAR(190)  NOT NULL,
    message    TEXT          NOT NULL,
    status     ENUM('open','pending','closed') NOT NULL DEFAULT 'open',
    created_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ticket_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(60)  NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;