-- Create database
CREATE DATABASE IF NOT EXISTS carbon_footprint;
USE carbon_footprint;

-- Table for general information
CREATE TABLE IF NOT EXISTS general_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    industry VARCHAR(100),
    company_size VARCHAR(100),
    company_revenue VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table for Scope 1 emissions
CREATE TABLE IF NOT EXISTS scope1_emissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    general_id INT NOT NULL,
	stat_co2_emissions DECIMAL(12,4) DEFAULT NULL,
	mobile_co2_emissions DECIMAL(12,4) DEFAULT NULL,
	fugitive_co2_emissions DECIMAL(12,4) DEFAULT NULL,
    -- Total emissions
    total_scope1_emissions DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (general_id) REFERENCES general_info(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS stationary_combustion_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scope1_emission_id INT NOT NULL, -- Foreign key to link to scope1_emissions table
    fuel_type VARCHAR(100),
    input_unit VARCHAR(50),
    quantity_2021 DECIMAL(12,2) DEFAULT NULL, -- Increased precision, allowing NULL if not entered
    quantity_2022 DECIMAL(12,2) DEFAULT NULL,
    quantity_2023 DECIMAL(12,2) DEFAULT NULL,
    quantity_2024 DECIMAL(12,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Optional for this detail table

    FOREIGN KEY (scope1_emission_id) REFERENCES scope1_emissions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS mobile_combustion_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scope1_emission_id INT NOT NULL, -- Foreign key to link to scope1_emissions table
    fuel_type VARCHAR(100),
    input_unit VARCHAR(50),
    quantity_2021 DECIMAL(12,2) DEFAULT NULL,
    quantity_2022 DECIMAL(12,2) DEFAULT NULL,
    quantity_2023 DECIMAL(12,2) DEFAULT NULL,
    quantity_2024 DECIMAL(12,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Optional

    FOREIGN KEY (scope1_emission_id) REFERENCES scope1_emissions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS fugitive_emission_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scope1_emission_id INT NOT NULL, -- Foreign key
    gas_type VARCHAR(100),
    input_unit VARCHAR(50),
    quantity_2021 DECIMAL(12,4) DEFAULT NULL, -- Increased precision for potential small quantities * GWP
    quantity_2022 DECIMAL(12,4) DEFAULT NULL,
    quantity_2023 DECIMAL(12,4) DEFAULT NULL,
    quantity_2024 DECIMAL(12,4) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (scope1_emission_id) REFERENCES scope1_emissions(id) ON DELETE CASCADE
);

-- Main table for Scope 2 overall emissions
CREATE TABLE IF NOT EXISTS scope2_emissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    general_id INT NOT NULL,
    electricity_total_co2e DECIMAL(12,4) DEFAULT 0.0000, -- Total CO2e from all electricity sources
    steam_heat_cooling_total_co2e DECIMAL(12,4) DEFAULT 0.0000, -- Total CO2e from steam/heat/cooling
    include_steam_heat_cooling BOOLEAN DEFAULT FALSE, -- Whether the S/H/C section was included
    total_scope2_emissions DECIMAL(12,4) DEFAULT 0.0000, -- Grand total for Scope 2
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (general_id) REFERENCES general_info(id) ON DELETE CASCADE
);

-- Detail table for purchased electricity entries
CREATE TABLE IF NOT EXISTS electricity_consumption_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scope2_emission_id INT NOT NULL, -- Foreign key linking to scope2_emissions table
    region VARCHAR(255) NOT NULL,    -- e.g., 'Peninsular Malaysia - Tenaga Nasional Berhad'
    -- Input unit is always kWh for electricity as per your form
	input_unit VARCHAR(10) NOT NULL,
    quantity_2021 DECIMAL(12,2) DEFAULT NULL, -- kWh consumed
    quantity_2022 DECIMAL(12,2) DEFAULT NULL, -- kWh consumed
    quantity_2023 DECIMAL(12,2) DEFAULT NULL, -- kWh consumed
    quantity_2024 DECIMAL(12,2) DEFAULT NULL, -- kWh consumed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scope2_emission_id) REFERENCES scope2_emissions(id) ON DELETE CASCADE
);

-- Detail table for purchased steam, heat, and cooling entries
CREATE TABLE IF NOT EXISTS steam_heat_cooling_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    scope2_emission_id INT NOT NULL, -- Foreign key linking to scope2_emissions table
    energy_type VARCHAR(100) NOT NULL, -- 'Steam', 'Heat', or 'Cooling'
    -- Input unit is always metric tonnes for S/H/C as per your form
    quantity_2021 DECIMAL(12,2) DEFAULT NULL, -- metric tonnes consumed
    quantity_2022 DECIMAL(12,2) DEFAULT NULL, -- metric tonnes consumed
    quantity_2023 DECIMAL(12,2) DEFAULT NULL, -- metric tonnes consumed
    quantity_2024 DECIMAL(12,2) DEFAULT NULL, -- metric tonnes consumed
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (scope2_emission_id) REFERENCES scope2_emissions(id) ON DELETE CASCADE
);

-- Detail table for annual revenue
CREATE TABLE IF NOT EXISTS annual_revenues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    general_id INT NOT NULL,
    revenue_2021 DECIMAL(15,2) DEFAULT NULL, -- Allowing for larger revenue figures
    revenue_2022 DECIMAL(15,2) DEFAULT NULL,
    revenue_2023 DECIMAL(15,2) DEFAULT NULL,
    revenue_2024 DECIMAL(15,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (general_id) REFERENCES general_info(id) ON DELETE CASCADE,
    UNIQUE KEY unique_general_id (general_id) -- Assuming one set of revenues per general_info entry
);

-- Table for Admin Users
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL, -- Store hashed passwords, NOT plain text
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO `admins` (`id`, `name`, `username`, `password_hash`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Admin', 'admin', '$2y$10$xUzf.JO0IbYn7k5joPAKQeE9ECRwALd41jFbBSLQEpQ66IRyopwim', 'active', '2025-05-07 04:28:11', '2025-05-07 04:30:59');