-- ============================================================
-- MODULE 3: Operationele Veiligheid (PtW & MoC)
-- Phoenix Metals HSEQ VBS — Migratie
-- ============================================================

-- Legacy tabellen voor bestaande data
CREATE TABLE IF NOT EXISTS moc_items_legacy (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    legacy_id TEXT,
    title TEXT,
    description TEXT,
    status TEXT,
    created_at TEXT,
    source TEXT
);

CREATE TABLE IF NOT EXISTS ptw_items_legacy (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    legacy_id TEXT,
    title TEXT,
    description TEXT,
    status TEXT,
    created_at TEXT,
    source TEXT
);

-- MoC Requests
CREATE TABLE IF NOT EXISTS moc_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    moc_code TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    proposed_change TEXT,
    change_category TEXT DEFAULT 'other',
    change_scope TEXT DEFAULT 'minor',
    change_reason TEXT,
    requested_by TEXT,
    requested_by_role TEXT,
    department TEXT,
    affected_equipment TEXT,
    affected_area TEXT,
    related_substance_id INTEGER,
    related_risk_scenario_id INTEGER,
    atex_zone_affected INTEGER DEFAULT 0,
    training_required INTEGER DEFAULT 0,
    breaking_containment INTEGER DEFAULT 0,
    pssr_required INTEGER DEFAULT 0,
    target_date TEXT,
    status TEXT DEFAULT 'draft',
    priority TEXT DEFAULT 'medium',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    closed_at TEXT,
    closed_by TEXT,
    notes TEXT,
    FOREIGN KEY (related_substance_id) REFERENCES substance_library(id),
    FOREIGN KEY (related_risk_scenario_id) REFERENCES risk_scenarios(id)
);

-- MoC Approvals
CREATE TABLE IF NOT EXISTS moc_approvals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    moc_id INTEGER NOT NULL,
    stage TEXT NOT NULL,
    approver_name TEXT,
    approver_role TEXT,
    decision TEXT,
    comments TEXT,
    conditions TEXT,
    reviewed_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (moc_id) REFERENCES moc_requests(id) ON DELETE CASCADE
);

-- MoC Actions
CREATE TABLE IF NOT EXISTS moc_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    moc_id INTEGER NOT NULL,
    action_text TEXT NOT NULL,
    assigned_to TEXT,
    due_date TEXT,
    status TEXT DEFAULT 'open',
    priority TEXT DEFAULT 'medium',
    completion_date TEXT,
    verified_by TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (moc_id) REFERENCES moc_requests(id) ON DELETE CASCADE
);

-- PtW Permits
CREATE TABLE IF NOT EXISTS ptw_permits (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    permit_code TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    permit_type TEXT DEFAULT 'general',
    location TEXT,
    area_zone TEXT,
    related_substance_id INTEGER,
    requested_by TEXT,
    requested_by_role TEXT,
    contractor TEXT,
    work_description TEXT,
    risk_level TEXT DEFAULT 'standard',
    status TEXT DEFAULT 'draft',
    start_date TEXT,
    end_date TEXT,
    issuer_name TEXT,
    issuer_signed_at TEXT,
    closer_name TEXT,
    closer_signed_at TEXT,
    gas_testing_required INTEGER DEFAULT 0,
    loto_required INTEGER DEFAULT 0,
    isolation_verified INTEGER DEFAULT 0,
    continuous_monitoring INTEGER DEFAULT 0,
    hf_antidote_available INTEGER DEFAULT 0,
    fire_extinguisher_available INTEGER DEFAULT 0,
    atex_zone TEXT,
    gas_test_toxic_unit TEXT,
    ppe_respiratory TEXT,
    ppe_gloves TEXT,
    ppe_eye TEXT,
    ppe_body TEXT,
    emergency_equipment TEXT,
    special_instructions TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT,
    notes TEXT,
    FOREIGN KEY (related_substance_id) REFERENCES substance_library(id)
);

-- PtW Gas Tests
CREATE TABLE IF NOT EXISTS ptw_gas_tests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    permit_id INTEGER NOT NULL,
    test_type TEXT NOT NULL,
    reading_value REAL,
    reading_unit TEXT,
    result TEXT DEFAULT 'pass',
    tested_by TEXT,
    tested_at TEXT DEFAULT (datetime('now')),
    instrument_id TEXT,
    notes TEXT,
    FOREIGN KEY (permit_id) REFERENCES ptw_permits(id) ON DELETE CASCADE
);

-- PtW LOTO Points
CREATE TABLE IF NOT EXISTS ptw_loto_points (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    permit_id INTEGER NOT NULL,
    energy_type TEXT NOT NULL,
    equipment_tag TEXT,
    isolation_method TEXT,
    lock_number TEXT,
    status TEXT DEFAULT 'pending',
    verified_by TEXT,
    verified_at TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (permit_id) REFERENCES ptw_permits(id) ON DELETE CASCADE
);

-- PtW Inspections
CREATE TABLE IF NOT EXISTS ptw_inspections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    permit_id INTEGER NOT NULL,
    inspection_type TEXT NOT NULL,
    result TEXT DEFAULT 'satisfactory',
    inspector_name TEXT,
    inspected_at TEXT DEFAULT (datetime('now')),
    findings TEXT,
    corrective_action TEXT,
    FOREIGN KEY (permit_id) REFERENCES ptw_permits(id) ON DELETE CASCADE
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_moc_requests_status ON moc_requests(status);
CREATE INDEX IF NOT EXISTS idx_moc_requests_category ON moc_requests(change_category);
CREATE INDEX IF NOT EXISTS idx_moc_requests_scope ON moc_requests(change_scope);
CREATE INDEX IF NOT EXISTS idx_moc_actions_moc_id ON moc_actions(moc_id);
CREATE INDEX IF NOT EXISTS idx_moc_approvals_moc_id ON moc_approvals(moc_id);
CREATE INDEX IF NOT EXISTS idx_ptw_permits_status ON ptw_permits(status);
CREATE INDEX IF NOT EXISTS idx_ptw_permits_type ON ptw_permits(permit_type);
CREATE INDEX IF NOT EXISTS idx_ptw_gas_tests_permit_id ON ptw_gas_tests(permit_id);
CREATE INDEX IF NOT EXISTS idx_ptw_loto_points_permit_id ON ptw_loto_points(permit_id);
CREATE INDEX IF NOT EXISTS idx_ptw_inspections_permit_id ON ptw_inspections(permit_id);