-- ============================================================
-- ACL-RND-001 | Phase 2 — Strategy Schema
-- Database   : thejourn_acl_rnd
-- Schema     : strategy
-- Version    : V1
-- Description: Tables for Rulebook, Zones, Setups, and
--              Rule Parameters — forward-compatible with
--              Phase 4 Platform UI (multi-user, adjustable params)
-- Run on     : ArenHost PostgreSQL via Perl forwarder port 8443
-- ============================================================

-- ── 0. CREATE SCHEMA ─────────────────────────────────────────
CREATE SCHEMA IF NOT EXISTS strategy;

-- ============================================================
-- TABLE 1: strategy.rulebook_versions
-- Track rulebook versions — V1, V2, etc.
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.rulebook_versions (
    version_id      SERIAL PRIMARY KEY,
    version_code    VARCHAR(10)  NOT NULL UNIQUE,         -- 'V1', 'V2', ...
    description     TEXT,
    is_active       BOOLEAN      NOT NULL DEFAULT FALSE,
    locked_at       TIMESTAMPTZ,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Insert Rulebook V1
INSERT INTO strategy.rulebook_versions
    (version_code, description, is_active, locked_at)
VALUES
    ('V1', 'Initial rulebook — globally accepted definitions (SnD, SnR, SMC/ICT standard). All numeric params use default values, adjustable per user via platform UI.', TRUE, NOW())
ON CONFLICT (version_code) DO NOTHING;

-- ============================================================
-- TABLE 2: strategy.rules
-- Master rule definitions — one row per rule per version
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.rules (
    rule_id         SERIAL PRIMARY KEY,
    rule_code       VARCHAR(10)  NOT NULL,                -- 'R.01', 'R.02', ...
    version_code    VARCHAR(10)  NOT NULL DEFAULT 'V1',
    rule_name       TEXT         NOT NULL,
    rule_category   TEXT         NOT NULL,                -- 'bias', 'zone', 'entry', 'risk', 'filter', 'special'
    timeframe       TEXT[],                               -- {'H4'}, {'H1','H4'}, {'M15'}, {'ALL'}
    applies_to      TEXT[],                               -- {'XAUUSD','USTEC',...} or {'ALL'}
    is_binary       BOOLEAN      NOT NULL DEFAULT TRUE,   -- must always be TRUE in V1
    definition      TEXT         NOT NULL,
    logic_notes     TEXT,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (rule_code, version_code)
);

-- Insert 8 Core Rules V1
INSERT INTO strategy.rules
    (rule_code, version_code, rule_name, rule_category, timeframe, applies_to, is_binary, definition, logic_notes)
VALUES
    ('R.01', 'V1', 'Bias Confirmation',    'bias',    ARRAY['H4'],         ARRAY['ALL'],
     TRUE,
     'H4 BOS ke atas = BULLISH, entry LONG only. H4 BOS ke bawah = BEARISH, entry SHORT only. Tidak ada clear BOS = RANGING, trigger R.02.',
     'BOS detected via swing high/low dengan lookback N candles kiri dan kanan.'),

    ('R.02', 'V1', 'Ranging Bias Rule',    'bias',    ARRAY['H4'],         ARRAY['ALL'],
     TRUE,
     'Saat H4 RANGING: strategy shift ke SnR boundary. Harga dekat bottom boundary = LONG zone. Harga dekat top boundary = SHORT zone. Mid-range = no trade.',
     'Range boundary dihitung dari N candle H4 terakhir. Proximity threshold = 20% dari total range height.'),

    ('R.03', 'V1', 'Zone Validity',        'zone',    ARRAY['H1','H4'],    ARRAY['ALL'],
     TRUE,
     'Zone valid jika: (1) terbentuk dari impulse candle (range > 1.5x ATR14, body > 70% range), (2) belum ditembus >50% body zone, (3) ada clear origin candle. Zone 3x tested = LOW priority. Zone ditembus >threshold = BROKEN.',
     'Zone types: DEMAND (bullish), SUPPLY (bearish). Status: FRESH / TESTED / BROKEN.'),

    ('R.04', 'V1', 'SMC Confluence',       'zone',    ARRAY['H1'],         ARRAY['ALL'],
     TRUE,
     'Setiap elemen SMC yang hadir di area zone menambah strength score +1. OB: last counter-candle sebelum impulse move >= 2x body OB. FVG: gap wick candle-1 dan wick candle-3 >= 0.5x ATR14. Sweep: price briefly breaks equal H/L lalu reversal.',
     'SMC confluence adalah bonus, bukan syarat wajib di V1. Meningkatkan zone priority score.'),

    ('R.05', 'V1', 'Entry Trigger',        'entry',   ARRAY['M15'],        ARRAY['ALL'],
     TRUE,
     'Entry valid HANYA jika 3 kondisi terpenuhi bersamaan: (1) R.01 atau R.02 valid, (2) R.03 valid di area yang aligned dengan bias, (3) M15 menunjukkan rejection candle di area zone: pin bar (wick >= 2x body), engulfing (body close > body prev), atau BOS minor. Satu kondisi tidak terpenuhi = no entry.',
     'All 3 conditions must be TRUE simultaneously. No exception.'),

    ('R.06', 'V1', 'SL & TP Placement',   'risk',    ARRAY['ALL'],        ARRAY['ALL'],
     TRUE,
     'SL: di luar zone + buffer per pair. Max risk 2% modal per trade. Jika risk >2% dengan SL di posisi logis = kecilkan lot atau skip. TP1 di 1:2 RR (close 50% posisi, move SL ke BE). TP2 di next major structure. Minimum RR 1:2 sebelum entry — jika tidak tercapai = skip.',
     'SL buffer per pair: XAUUSD 0.8-1.2pt, USTEC 5-8pt, US500 3-5pt, GBPUSD 8-12pip, GBPJPY 12-18pip.'),

    ('R.07', 'V1', 'News & Session Filter','filter',  ARRAY['ALL'],        ARRAY['ALL'],
     TRUE,
     'No new entry 30min sebelum dan 60min sesudah high-impact news untuk pair relevan. Entry hanya dalam session window per pair: XAUUSD/GBPUSD/GBPJPY = Asia+London+NY. USTEC/US500 = NY only (19:30-23:00 WIB).',
     'News flags sourced from market.news_flags table (Phase 1). Session windows stored in config.session_definitions.'),

    ('R.08', 'V1', 'Correlated Pair Rule', 'special', ARRAY['ALL'],        ARRAY['USTEC','US500'],
     TRUE,
     'USTEC dan US500 boleh dienter bersamaan jika keduanya memiliki setup valid secara independen (R.01-R.07 masing-masing terpenuhi). Risk dihitung per posisi — max 2% per trade berlaku masing-masing.',
     'Simultaneous entry on USTEC+US500 is permitted. Total exposure may reach 4% — this is intentional.')
ON CONFLICT (rule_code, version_code) DO NOTHING;

-- ============================================================
-- TABLE 3: strategy.rule_parameters
-- Default parameter values per rule — adjustable per user
-- Forward-compatible with Phase 4 Platform UI
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.rule_parameters (
    param_id        SERIAL PRIMARY KEY,
    rule_code       VARCHAR(10)  NOT NULL,
    version_code    VARCHAR(10)  NOT NULL DEFAULT 'V1',
    user_id         TEXT         NOT NULL DEFAULT 'SYSTEM',  -- 'SYSTEM' = global default
    param_name      TEXT         NOT NULL,
    param_value     TEXT         NOT NULL,                   -- stored as text, cast at query time
    param_type      TEXT         NOT NULL,                   -- 'INTEGER', 'FLOAT', 'PCT', 'TEXT'
    param_min       TEXT,
    param_max       TEXT,
    param_unit      TEXT,
    description     TEXT,
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (rule_code, version_code, user_id, param_name)
);

-- Insert default parameters for all rules (user_id = 'SYSTEM' means global default)
INSERT INTO strategy.rule_parameters
    (rule_code, version_code, user_id, param_name, param_value, param_type, param_min, param_max, param_unit, description)
VALUES
    -- R.01 params
    ('R.01', 'V1', 'SYSTEM', 'swing_lookback_n',         '3',    'INTEGER', '2',    '10',   'candles', 'Jumlah candle kiri & kanan untuk deteksi swing high/low'),

    -- R.02 params
    ('R.02', 'V1', 'SYSTEM', 'range_lookback',            '50',   'INTEGER', '20',   '100',  'candles', 'Jumlah candle H4 untuk define range boundary'),
    ('R.02', 'V1', 'SYSTEM', 'boundary_proximity_pct',    '20',   'FLOAT',   '10',   '35',   'pct',     'Persentase range height yang dianggap dekat boundary'),

    -- R.03 params
    ('R.03', 'V1', 'SYSTEM', 'atr_period',                '14',   'INTEGER', '7',    '21',   'candles', 'Period ATR untuk ukur volatilitas candle'),
    ('R.03', 'V1', 'SYSTEM', 'atr_multiplier',            '1.5',  'FLOAT',   '1.2',  '2.5',  'x',       'Minimum candle range relatif ATR untuk dianggap impulse'),
    ('R.03', 'V1', 'SYSTEM', 'body_threshold',            '70',   'FLOAT',   '50',   '85',   'pct',     'Minimum body size sebagai persen dari total candle range'),
    ('R.03', 'V1', 'SYSTEM', 'penetration_threshold',     '50',   'FLOAT',   '30',   '70',   'pct',     'Batas penetrasi zone sebelum dianggap BROKEN'),
    ('R.03', 'V1', 'SYSTEM', 'max_tests_before_weak',     '3',    'INTEGER', '2',    '5',    'count',   'Jumlah test sebelum zone strength turun ke LOW'),

    -- R.04 params
    ('R.04', 'V1', 'SYSTEM', 'ob_impulse_multiplier',     '2.0',  'FLOAT',   '1.5',  '3.0',  'x',       'Minimum impulse move relatif body OB untuk validasi Order Block'),
    ('R.04', 'V1', 'SYSTEM', 'fvg_min_size_atr',          '0.5',  'FLOAT',   '0.2',  '1.0',  'x_atr',   'Minimum FVG gap size relatif ATR(14)'),
    ('R.04', 'V1', 'SYSTEM', 'equal_hl_lookback',         '5',    'INTEGER', '3',    '15',   'candles', 'Jumlah candle untuk deteksi equal highs/lows'),
    ('R.04', 'V1', 'SYSTEM', 'equal_hl_tolerance_pip',    '3',    'FLOAT',   '1',    '10',   'pip',     'Toleransi pip untuk dua high/low dianggap equal'),

    -- R.05 params
    ('R.05', 'V1', 'SYSTEM', 'pin_bar_wick_ratio',        '2.0',  'FLOAT',   '1.5',  '3.0',  'x',       'Minimum wick panjang relatif body untuk dianggap pin bar'),
    ('R.05', 'V1', 'SYSTEM', 'engulf_body_pct',           '100',  'FLOAT',   '80',   '100',  'pct',     'Minimum persen body candle sebelumnya yang harus ditutupi engulfing'),
    ('R.05', 'V1', 'SYSTEM', 'zone_entry_tolerance',      '10',   'FLOAT',   '0',    '20',   'pip',     'Toleransi harga di luar zone boundary yang masih dianggap di area zone'),

    -- R.06 params
    ('R.06', 'V1', 'SYSTEM', 'max_risk_pct',              '2.0',  'FLOAT',   '0.5',  '3.0',  'pct',     'Maximum risk per trade sebagai persentase modal'),
    ('R.06', 'V1', 'SYSTEM', 'min_rr_ratio',              '2.0',  'FLOAT',   '1.5',  '4.0',  'ratio',   'Minimum Risk:Reward ratio sebelum entry diizinkan'),
    ('R.06', 'V1', 'SYSTEM', 'tp1_rr',                    '2.0',  'FLOAT',   '1.5',  '3.0',  'ratio',   'RR target untuk TP1'),
    ('R.06', 'V1', 'SYSTEM', 'tp1_close_pct',             '50',   'FLOAT',   '30',   '70',   'pct',     'Persentase posisi yang diclose saat TP1 hit'),

    -- R.06 SL buffer per pair (stored separately for per-pair lookup)
    ('R.06', 'V1', 'SYSTEM', 'sl_buffer_XAUUSD',          '1.0',  'FLOAT',   '0.5',  '2.0',  'point',   'SL buffer untuk XAUUSD'),
    ('R.06', 'V1', 'SYSTEM', 'sl_buffer_USTEC',           '6.0',  'FLOAT',   '3.0',  '12.0', 'point',   'SL buffer untuk USTEC (NAS100)'),
    ('R.06', 'V1', 'SYSTEM', 'sl_buffer_US500',           '4.0',  'FLOAT',   '2.0',  '8.0',  'point',   'SL buffer untuk US500'),
    ('R.06', 'V1', 'SYSTEM', 'sl_buffer_GBPUSD',          '10',   'FLOAT',   '5',    '20',   'pip',     'SL buffer untuk GBPUSD'),
    ('R.06', 'V1', 'SYSTEM', 'sl_buffer_GBPJPY',          '15',   'FLOAT',   '8',    '25',   'pip',     'SL buffer untuk GBPJPY'),

    -- R.07 params
    ('R.07', 'V1', 'SYSTEM', 'pre_news_buffer_min',       '30',   'INTEGER', '15',   '60',   'minutes', 'Menit sebelum news — no new entry'),
    ('R.07', 'V1', 'SYSTEM', 'post_news_buffer_min',      '60',   'INTEGER', '30',   '120',  'minutes', 'Menit sesudah news — no new entry'),
    ('R.07', 'V1', 'SYSTEM', 'news_impact_level',         'HIGH', 'TEXT',    NULL,   NULL,   NULL,      'Minimum impact level yang di-filter: MEDIUM atau HIGH')

ON CONFLICT (rule_code, version_code, user_id, param_name) DO NOTHING;

-- ============================================================
-- TABLE 4: strategy.zones
-- Detected zones from Phase 3 engine
-- Pre-created here so schema is ready before Phase 3
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.zones (
    zone_id         BIGSERIAL PRIMARY KEY,
    pair            TEXT         NOT NULL,
    timeframe       TEXT         NOT NULL,               -- 'H4', 'H1'
    zone_type       TEXT         NOT NULL,               -- 'DEMAND', 'SUPPLY'
    zone_top        NUMERIC(18,5) NOT NULL,
    zone_bottom     NUMERIC(18,5) NOT NULL,
    origin_time     TIMESTAMPTZ  NOT NULL,               -- candle yang membentuk zone
    status          TEXT         NOT NULL DEFAULT 'FRESH', -- 'FRESH', 'TESTED', 'BROKEN'
    strength        SMALLINT     NOT NULL DEFAULT 3,     -- 1 (weak) to 5 (strong)
    times_tested    SMALLINT     NOT NULL DEFAULT 0,
    ob_present      BOOLEAN      NOT NULL DEFAULT FALSE,
    fvg_present     BOOLEAN      NOT NULL DEFAULT FALSE,
    sweep_present   BOOLEAN      NOT NULL DEFAULT FALSE,
    rulebook_version VARCHAR(10) NOT NULL DEFAULT 'V1',
    detected_at     TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_zones_pair_tf
    ON strategy.zones (pair, timeframe);

CREATE INDEX IF NOT EXISTS idx_zones_status
    ON strategy.zones (status);

CREATE INDEX IF NOT EXISTS idx_zones_origin_time
    ON strategy.zones (origin_time DESC);

-- ============================================================
-- TABLE 5: strategy.setups
-- Valid trade setups detected by Phase 3 engine
-- One row = one setup that passed all rules R.01-R.07
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.setups (
    setup_id        BIGSERIAL PRIMARY KEY,
    pair            TEXT         NOT NULL,
    setup_time      TIMESTAMPTZ  NOT NULL,               -- M15 entry candle timestamp
    direction       TEXT         NOT NULL,               -- 'LONG', 'SHORT'
    bias_h4         TEXT         NOT NULL,               -- 'BULLISH', 'BEARISH', 'RANGING'
    zone_id         BIGINT       REFERENCES strategy.zones(zone_id),
    zone_h1_valid   BOOLEAN      NOT NULL DEFAULT FALSE,
    zone_strength   SMALLINT,                            -- 1 to 5
    entry_trigger   TEXT,                                -- 'PIN_BAR', 'ENGULFING', 'BOS_MINOR'
    session         TEXT,                                -- 'ASIA', 'LONDON', 'NY', 'OVERLAP'
    entry_price     NUMERIC(18,5),
    sl_price        NUMERIC(18,5),
    tp1_price       NUMERIC(18,5),
    tp2_price       NUMERIC(18,5),
    rr_ratio        NUMERIC(5,2),
    risk_pct        NUMERIC(5,2),
    is_news_filtered BOOLEAN     NOT NULL DEFAULT FALSE, -- TRUE = filtered out by R.07
    smc_confluence  TEXT[],                              -- {'OB','FVG','SWEEP'}
    r01_pass        BOOLEAN,
    r02_pass        BOOLEAN,
    r03_pass        BOOLEAN,
    r04_pass        BOOLEAN,
    r05_pass        BOOLEAN,
    r06_pass        BOOLEAN,
    r07_pass        BOOLEAN,
    all_rules_met   BOOLEAN      GENERATED ALWAYS AS (
                        COALESCE(r01_pass OR r02_pass, FALSE)
                        AND COALESCE(r03_pass, FALSE)
                        AND COALESCE(r05_pass, FALSE)
                        AND COALESCE(r06_pass, FALSE)
                        AND COALESCE(r07_pass, FALSE)
                    ) STORED,
    rulebook_version VARCHAR(10) NOT NULL DEFAULT 'V1',
    param_snapshot  JSONB,                               -- snapshot of params used at detection time
    created_by      TEXT        NOT NULL DEFAULT 'SYSTEM', -- for multi-user Phase 4
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_setups_pair_time
    ON strategy.setups (pair, setup_time DESC);

CREATE INDEX IF NOT EXISTS idx_setups_all_rules
    ON strategy.setups (all_rules_met);

CREATE INDEX IF NOT EXISTS idx_setups_direction
    ON strategy.setups (direction);

CREATE INDEX IF NOT EXISTS idx_setups_session
    ON strategy.setups (session);

-- ============================================================
-- TABLE 6: strategy.backtest_runs
-- Track each backtest execution — ready for Phase 3
-- ============================================================
CREATE TABLE IF NOT EXISTS strategy.backtest_runs (
    run_id          SERIAL PRIMARY KEY,
    run_name        TEXT,
    rulebook_version VARCHAR(10) NOT NULL DEFAULT 'V1',
    pairs           TEXT[],                              -- pairs included in this run
    date_from       DATE         NOT NULL,
    date_to         DATE         NOT NULL,
    timeframes      TEXT[]       NOT NULL DEFAULT ARRAY['M15','H1','H4'],
    sessions        TEXT[],
    param_snapshot  JSONB,                               -- full param snapshot at run time
    created_by      TEXT         NOT NULL DEFAULT 'SYSTEM',
    status          TEXT         NOT NULL DEFAULT 'PENDING', -- 'PENDING','RUNNING','DONE','FAILED'
    started_at      TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    total_setups    INTEGER,
    valid_setups    INTEGER,
    notes           TEXT,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- ============================================================
-- HELPER VIEW: strategy.v_active_params
-- Easy lookup of current active params per rule
-- ============================================================
CREATE OR REPLACE VIEW strategy.v_active_params AS
SELECT
    rp.rule_code,
    rp.version_code,
    rp.user_id,
    rp.param_name,
    rp.param_value,
    rp.param_type,
    rp.param_min,
    rp.param_max,
    rp.param_unit,
    rp.description,
    r.rule_name,
    r.rule_category
FROM strategy.rule_parameters rp
JOIN strategy.rules r
    ON r.rule_code = rp.rule_code
   AND r.version_code = rp.version_code
WHERE rp.version_code = 'V1'
ORDER BY rp.rule_code, rp.param_name;

-- ============================================================
-- HELPER VIEW: strategy.v_zone_summary
-- Quick zone overview for monitoring
-- ============================================================
CREATE OR REPLACE VIEW strategy.v_zone_summary AS
SELECT
    pair,
    timeframe,
    zone_type,
    status,
    COUNT(*)                        AS zone_count,
    ROUND(AVG(strength), 1)         AS avg_strength,
    MAX(detected_at)                AS latest_detected
FROM strategy.zones
GROUP BY pair, timeframe, zone_type, status
ORDER BY pair, timeframe, zone_type, status;

-- ============================================================
-- AUDIT NOTE
-- ============================================================
INSERT INTO audit.schema_changes (schema_name, change_description, changed_by)
VALUES ('strategy', 'Phase 2 complete — strategy schema created: 6 tables, 2 views, 8 rules, 27 default parameters. Rulebook V1 locked.', 'acl_phase2')
ON CONFLICT DO NOTHING;

-- ============================================================
-- VERIFY
-- ============================================================
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'strategy'
ORDER BY tablename;

SELECT rule_code, rule_name, rule_category, is_binary
FROM strategy.rules
ORDER BY rule_code;

SELECT COUNT(*) AS total_params
FROM strategy.rule_parameters
WHERE user_id = 'SYSTEM';