-- Create database with proper character set
CREATE DATABASE IF NOT EXISTS emp_audit_system 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Create and grant privileges to application user
CREATE USER IF NOT EXISTS 'emp_audit_user'@'localhost' 
IDENTIFIED BY 'SecurePass123!';

GRANT ALL PRIVILEGES ON emp_audit_system.* 
TO 'emp_audit_user'@'localhost';

FLUSH PRIVILEGES;

USE emp_audit_system;

-- Users table with enhanced security fields
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('auditor', 'manager', 'admin') DEFAULT 'auditor',
    is_active BOOLEAN DEFAULT TRUE,
    last_login DATETIME,
    failed_login_attempts INT DEFAULT 0,
    password_reset_token VARCHAR(255),
    password_reset_expires DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_username (username),
    INDEX idx_users_email (email),
    INDEX idx_users_role (role)
) ENGINE=InnoDB;

-- Establishments table with comprehensive fields
CREATE TABLE IF NOT EXISTS establishments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100) NOT NULL,
    type ENUM('lodge', 'hotel', 'camp', 'resort', 'other') NOT NULL,
    description TEXT,
    gps_coordinates VARCHAR(50),
    certificate_number VARCHAR(50),
    certificate_issue_date DATE,
    certificate_expiry DATE,
    contact_person VARCHAR(100) NOT NULL,
    contact_email VARCHAR(100) NOT NULL,
    contact_phone VARCHAR(20) NOT NULL,
    environmental_officer VARCHAR(100),
    environmental_officer_contact VARCHAR(20),
    created_by INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_establishments_name (name),
    INDEX idx_establishments_location (location),
    INDEX idx_establishments_type (type),
    INDEX idx_establishments_cert_expiry (certificate_expiry)
) ENGINE=InnoDB;

-- Environmental regulations table
CREATE TABLE IF NOT EXISTS regulations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_code CHAR(2) NOT NULL,
    regulation_name VARCHAR(100) NOT NULL,
    aspect_type ENUM(
        'emp_systems',
        'community_relations',
        'biodiversity',
        'land_degradation',
        'sense_of_place',
        'water_usage',
        'energy_usage',
        'pollution',
        'motorboats',
        'solid_waste'
    ) NOT NULL,
    requirement TEXT NOT NULL,
    legal_reference VARCHAR(100) NOT NULL,
    compliance_criteria TEXT NOT NULL,
    penalty_clause TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_regulations_country (country_code),
    INDEX idx_regulations_aspect (aspect_type),
    INDEX idx_regulations_reference (legal_reference)
) ENGINE=InnoDB;

-- Audits main table
CREATE TABLE IF NOT EXISTS audits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    establishment_id INT NOT NULL,
    user_id INT NOT NULL,
    audit_date DATE NOT NULL,
    next_audit_date DATE,
    status ENUM('draft', 'submitted', 'under_review', 'approved', 'rejected', 'archived') DEFAULT 'draft',
    overall_compliance_score DECIMAL(5,2),
    submitted_at DATETIME,
    reviewed_by INT,
    reviewed_at DATETIME,
    approved_by INT,
    approved_at DATETIME,
    approval_notes TEXT,
    observations TEXT,
    recommendations TEXT,
    is_complete BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (establishment_id) REFERENCES establishments(id) ON DELETE RESTRICT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audits_establishment (establishment_id),
    INDEX idx_audits_user (user_id),
    INDEX idx_audits_status (status),
    INDEX idx_audits_date (audit_date),
    INDEX idx_audits_compliance (overall_compliance_score)
) ENGINE=InnoDB;

-- Audit aspects table
CREATE TABLE IF NOT EXISTS audit_aspects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    audit_id INT NOT NULL,
    regulation_id INT,
    aspect_type ENUM(
        'emp_systems',
        'community_relations',
        'biodiversity',
        'land_degradation',
        'sense_of_place',
        'water_usage',
        'energy_usage',
        'pollution',
        'motorboats',
        'solid_waste'
    ) NOT NULL,
    is_compliant BOOLEAN DEFAULT FALSE,
    compliance_score DECIMAL(5,2),
    notes TEXT,
    recommendations TEXT,
    corrective_actions TEXT,
    target_completion_date DATE,
    responsible_party VARCHAR(100),
    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 (regulation_id) REFERENCES regulations(id) ON DELETE SET NULL,
    INDEX idx_aspects_audit (audit_id),
    INDEX idx_aspects_compliance (is_compliant),
    INDEX idx_aspects_type (aspect_type)
) ENGINE=InnoDB;

-- Biodiversity-specific details
CREATE TABLE IF NOT EXISTS biodiversity_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    audit_aspect_id INT NOT NULL,
    invasive_plants_status ENUM('none', 'low', 'medium', 'high', 'na') DEFAULT 'na',
    invasive_species_list TEXT,
    endangered_species_present BOOLEAN DEFAULT FALSE,
    endangered_species_details TEXT,
    conservation_measures TEXT,
    monitoring_program TEXT,
    details TEXT,
    action_plan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (audit_aspect_id) REFERENCES audit_aspects(id) ON DELETE CASCADE,
    INDEX idx_biodiversity_aspect (audit_aspect_id)
) ENGINE=InnoDB;

-- 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);

-- Migration script to create audits table for EMP Monitoring System

CREATE TABLE IF NOT EXISTS audits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    establishment_id INT NOT NULL,
    user_id INT NOT NULL,
    audit_date DATE,
    location VARCHAR(255),
    evidence TEXT,
    observations TEXT,
    recommendations TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (establishment_id) REFERENCES establishments(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
