-- IT System Handover Module - Unified Setup Script (SOW & BAST)
-- Continuous Lowercase Table Names
-- Lokasi Migration: d:\Development\productions\sapps-backend\migrations\20260312-handover-module-setup.sql

-- 1. Create handoverrequests table (Scope of Work - SOW)
CREATE TABLE IF NOT EXISTS handoverrequests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creation DATETIME NOT NULL,
    modified DATETIME NOT NULL,
    modified_by INT NOT NULL,
    owner INT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 1,

    request_number VARCHAR(50) NOT NULL UNIQUE,
    subject VARCHAR(255),
    applicant_id INT NOT NULL,
    department_id INT NOT NULL,
    system_description TEXT,
    renewal_reason TEXT,
    renewal_objective TEXT,
    expected_impact TEXT,
    additional_notes TEXT,
    supervisor_id INT,
    status VARCHAR(20) NOT NULL DEFAULT 'Draft', -- Draft, Pending, Approved, Completed, Void

    doc_no VARCHAR(50),
    issue_date DATE,
    rev_no VARCHAR(10) DEFAULT '0',
    rev_date DATE,

    INDEX idx_handoverrequests_applicant (applicant_id),
    INDEX idx_handoverrequests_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Create handovercompletions table (Handover Completion - BAST)
CREATE TABLE IF NOT EXISTS handovercompletions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creation DATETIME NOT NULL,
    modified DATETIME NOT NULL,
    modified_by INT NOT NULL,
    owner INT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 1,

    handover_number VARCHAR(50) NOT NULL UNIQUE,
    request_id INT NOT NULL,
    app_id INT, -- Can be NULL if it's a new module not yet in apps master
    system_version VARCHAR(50),
    handover_date DATE NOT NULL,
    location VARCHAR(255),
    receiver_id INT NOT NULL,
    programmer_id INT NOT NULL,
    it_manager_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Draft', -- Draft, Approved, Void

    doc_no VARCHAR(50),
    issue_date DATE,
    rev_no VARCHAR(10) DEFAULT '0',
    rev_date DATE,

    INDEX idx_handovercompletions_request (request_id),
    INDEX idx_handovercompletions_receiver (receiver_id),
    FOREIGN KEY (request_id) REFERENCES handoverrequests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Create handoverfeatures table
CREATE TABLE IF NOT EXISTS handoverfeatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creation DATETIME NOT NULL,
    modified DATETIME NOT NULL,
    modified_by INT NOT NULL,
    owner INT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 1,

    handover_id INT NOT NULL,
    feature_name VARCHAR(255) NOT NULL,
    description TEXT,

    INDEX idx_handoverfeatures_handover (handover_id),
    FOREIGN KEY (handover_id) REFERENCES handovercompletions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Create handovertests table
CREATE TABLE IF NOT EXISTS handovertests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    creation DATETIME NOT NULL,
    modified DATETIME NOT NULL,
    modified_by INT NOT NULL,
    owner INT NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT 1,

    handover_id INT NOT NULL,
    module_name VARCHAR(100),
    test_scenario TEXT,
    test_type VARCHAR(100) NOT NULL,
    test_result VARCHAR(50) NOT NULL,
    status VARCHAR(50),
    notes TEXT,

    INDEX idx_handovertests_handover (handover_id),
    FOREIGN KEY (handover_id) REFERENCES handovercompletions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;