-- Create handover_requests table
CREATE TABLE IF NOT EXISTS handover_requests (
    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,
    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_handover_requests_applicant (applicant_id),
    INDEX idx_handover_requests_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create handover_completions table (BAST)
CREATE TABLE IF NOT EXISTS handover_completions (
    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_handover_completions_request (request_id),
    INDEX idx_handover_completions_receiver (receiver_id),
    FOREIGN KEY (request_id) REFERENCES handover_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create handover_features table
CREATE TABLE IF NOT EXISTS handover_features (
    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_handover_features_handover (handover_id),
    FOREIGN KEY (handover_id) REFERENCES handover_completions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create handover_tests table
CREATE TABLE IF NOT EXISTS handover_tests (
    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,
    test_type VARCHAR(100) NOT NULL,
    test_result VARCHAR(50) NOT NULL,
    notes TEXT,

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