-- Migration script to create tables for structured EMP Monitoring System

-- Regions table
CREATE TABLE IF NOT EXISTS regions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Question categories table
CREATE TABLE IF NOT EXISTS question_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Standardized questions table
CREATE TABLE IF NOT EXISTS standardized_questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    region_id INT NOT NULL,
    question_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES question_categories(id) ON DELETE CASCADE,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE,
    INDEX idx_questions_category (category_id),
    INDEX idx_questions_region (region_id)
) ENGINE=InnoDB;

-- Audit answers table to store responses per question
CREATE TABLE IF NOT EXISTS audit_answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    audit_id INT NOT NULL,
    question_id INT NOT NULL,
    answer ENUM('yes', 'no', 'na') NOT NULL,
    action_taken TEXT,
    responsible_person VARCHAR(100),
    audit_finding TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (audit_id) REFERENCES audits(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES standardized_questions(id) ON DELETE CASCADE,
    INDEX idx_answers_audit (audit_id),
    INDEX idx_answers_question (question_id)
) ENGINE=InnoDB;

-- Evidence linked to audit answers (per question)
ALTER TABLE evidence
ADD COLUMN audit_answer_id INT NULL AFTER audit_aspect_id,
ADD FOREIGN KEY (audit_answer_id) REFERENCES audit_answers(id) ON DELETE CASCADE,
ADD INDEX idx_evidence_answer (audit_answer_id);
