-- Stock Cockpit V1
-- Target: MariaDB 10.11.x / MySQL-compatible
-- Same schema for production and Laragon.
-- Database name is configured outside this file; create the DB first.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS analysis_results;
DROP TABLE IF EXISTS stock_thesis;
DROP TABLE IF EXISTS watchlist;
DROP TABLE IF EXISTS price_snapshots;
DROP TABLE IF EXISTS pending_orders;
DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS stocks;
DROP TABLE IF EXISTS advisor_rules;
DROP TABLE IF EXISTS app_settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(80) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    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,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE advisor_rules (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    version VARCHAR(30) NOT NULL,
    rule_name VARCHAR(120) NOT NULL,
    rule_text TEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_advisor_rules_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stocks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ticker VARCHAR(12) NOT NULL,
    company_name VARCHAR(200) NOT NULL,
    asset_type ENUM('stock','etf') NOT NULL DEFAULT 'stock',
    market VARCHAR(30) NOT NULL DEFAULT 'IDX',
    currency CHAR(3) NOT NULL DEFAULT 'IDR',
    is_active TINYINT(1) NOT NULL DEFAULT 1,

    -- Practical sharia classification used by this app.
    -- This is a current classification, not a historical DES ledger.
    sharia_status ENUM('sharia','non_sharia','unknown') NOT NULL DEFAULT 'unknown',
    sharia_source VARCHAR(50) NULL,
    sharia_checked_at DATETIME NULL,

    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_stocks_ticker (ticker),
    KEY idx_stocks_sharia (sharia_status),
    KEY idx_stocks_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NOT NULL,

    transaction_type ENUM('BUY','SELL','DIVIDEND','FEE','ADJUSTMENT') NOT NULL,
    transaction_date DATE NOT NULL,

    quantity_shares INT UNSIGNED NOT NULL DEFAULT 0,
    lots DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    price_per_share DECIMAL(18,4) NOT NULL DEFAULT 0.0000,

    gross_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    fees DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    net_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,

    -- For SELL transactions, record the cost basis used at the time.
    cost_basis DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    realized_pnl DECIMAL(20,2) NOT NULL DEFAULT 0.00,

    broker VARCHAR(50) NULL DEFAULT 'Bibit',
    reference_no VARCHAR(100) NULL,
    notes TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_transactions_stock_date (stock_id, transaction_date, id),
    KEY idx_transactions_type_date (transaction_type, transaction_date),
    CONSTRAINT fk_transactions_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pending_orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NOT NULL,
    order_type ENUM('BUY','SELL') NOT NULL,
    order_date DATE NOT NULL,

    quantity_shares INT UNSIGNED NOT NULL DEFAULT 0,
    lots DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    limit_price DECIMAL(18,4) NOT NULL DEFAULT 0.0000,

    status ENUM('PENDING','PARTIAL','MATCHED','CANCELLED','EXPIRED') NOT NULL DEFAULT 'PENDING',
    matched_shares INT UNSIGNED NOT NULL DEFAULT 0,
    matched_price DECIMAL(18,4) NULL,

    broker VARCHAR(50) NULL DEFAULT 'Bibit',
    reference_no VARCHAR(100) NULL,
    notes TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_pending_stock_status (stock_id, status),
    KEY idx_pending_status_date (status, order_date),
    CONSTRAINT fk_pending_orders_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE price_snapshots (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NOT NULL,
    captured_at DATETIME NOT NULL,

    price DECIMAL(18,4) NOT NULL,
    open_price DECIMAL(18,4) NULL,
    high_price DECIMAL(18,4) NULL,
    low_price DECIMAL(18,4) NULL,
    previous_close DECIMAL(18,4) NULL,
    volume BIGINT UNSIGNED NULL,
    value_traded DECIMAL(24,2) NULL,

    change_amount DECIMAL(18,4) NULL,
    change_percent DECIMAL(10,4) NULL,

    source VARCHAR(80) NOT NULL DEFAULT 'unknown',
    source_reference VARCHAR(255) NULL,
    data_quality ENUM('OK','STALE','PARTIAL','ERROR') NOT NULL DEFAULT 'OK',

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_price_stock_time_source (stock_id, captured_at, source),
    KEY idx_price_stock_time (stock_id, captured_at),
    KEY idx_price_captured_at (captured_at),
    CONSTRAINT fk_price_snapshots_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE watchlist (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NOT NULL,
    priority ENUM('LOW','NORMAL','HIGH') NOT NULL DEFAULT 'NORMAL',
    status ENUM('WATCH','REVIEW','READY','ARCHIVED') NOT NULL DEFAULT 'WATCH',
    target_price DECIMAL(18,4) NULL,
    notes TEXT NULL,
    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_watchlist_stock (stock_id),
    KEY idx_watchlist_status_priority (status, priority),
    CONSTRAINT fk_watchlist_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stock_thesis (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NOT NULL,
    thesis_status ENUM('OPEN','REVIEW','BROKEN','CLOSED') NOT NULL DEFAULT 'OPEN',
    thesis TEXT NULL,
    catalyst TEXT NULL,
    risk_notes TEXT NULL,
    next_review_date DATE NULL,
    updated_by BIGINT UNSIGNED NULL,
    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_stock_thesis_stock (stock_id),
    KEY idx_thesis_status_review (thesis_status, next_review_date),
    CONSTRAINT fk_stock_thesis_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_stock_thesis_user
        FOREIGN KEY (updated_by) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE analysis_results (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    stock_id BIGINT UNSIGNED NULL,
    analysis_date DATETIME NOT NULL,
    analysis_type ENUM('PORTFOLIO','STOCK','WATCHLIST','MARKET_UPDATE','SYSTEM') NOT NULL DEFAULT 'STOCK',

    signal ENUM('NORMAL','PROFIT_REVIEW','LOSS_REVIEW','THESIS_REVIEW','SHARIA_REVIEW','DATA_STALE','WATCH') NOT NULL DEFAULT 'NORMAL',
    severity ENUM('INFO','LOW','MEDIUM','HIGH') NOT NULL DEFAULT 'INFO',

    score DECIMAL(8,2) NULL,
    summary VARCHAR(500) NULL,
    details TEXT NULL,
    rule_version VARCHAR(30) NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    KEY idx_analysis_stock_date (stock_id, analysis_date),
    KEY idx_analysis_signal_date (signal, analysis_date),
    KEY idx_analysis_date (analysis_date),
    CONSTRAINT fk_analysis_stock
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;