-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS collectorsdexnew;
USE collectorsdexnew;

-- Users table
CREATE TABLE IF NOT EXISTS Users (
    id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    password VARCHAR(255) NOT NULL,
    role ENUM('USER', 'ADMIN', 'Crew', 'Sitemanager', 'Engineer', 'Finance', 'Projectleider') DEFAULT 'Crew',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sync History table to track synchronization
CREATE TABLE IF NOT EXISTS SyncHistory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sync_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    projects_added INT DEFAULT 0,
    projects_updated INT DEFAULT 0,
    functions_added INT DEFAULT 0,
    functions_updated INT DEFAULT 0,
    function_groups_added INT DEFAULT 0,
    function_groups_updated INT DEFAULT 0,
    hardware_categories_added INT DEFAULT 0,
    hardware_categories_updated INT DEFAULT 0,
    materials_added INT DEFAULT 0,
    materials_updated INT DEFAULT 0,
    sync_status ENUM('SUCCESS', 'FAILED') NOT NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sync_date (sync_date)
);

-- Projects table
CREATE TABLE IF NOT EXISTS Projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT UNIQUE NOT NULL,
    number VARCHAR(50),
    displayname VARCHAR(255) NOT NULL,
    planperiod_start DATETIME,
    planperiod_end DATETIME,
    status ENUM('Gepland', 'Actief', 'Afgerond') DEFAULT 'Gepland',
    is_deleted BOOLEAN DEFAULT FALSE,
    last_sync_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rentman_id (rentman_id),
    INDEX idx_status (status),
    INDEX idx_planperiod (planperiod_start, planperiod_end),
    CONSTRAINT fk_last_sync
        FOREIGN KEY (last_sync_id) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- Crew table
CREATE TABLE IF NOT EXISTS Crew (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT UNIQUE NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    firstname VARCHAR(100),
    middle_name VARCHAR(100),
    lastname VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(50),
    street VARCHAR(255),
    housenumber VARCHAR(50),
    city VARCHAR(100),
    postal_code VARCHAR(20),
    last_synced INT,
    GCS_JOINED BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rentman_id (rentman_id),
    INDEX idx_email (email),
    CONSTRAINT fk_crew_last_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- ProjectCrew table
CREATE TABLE IF NOT EXISTS ProjectCrew (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT NOT NULL,
    project_id INT NOT NULL,
    crew_rentman_id INT NOT NULL,
    planperiod_start DATETIME,
    planperiod_end DATETIME,
    hours_planned DECIMAL(10,2) DEFAULT 0,
    actual_hours DECIMAL(10,2) DEFAULT 0,
    actual_km INT DEFAULT 0,
    had_breakfast BOOLEAN DEFAULT FALSE,
    had_lunch BOOLEAN DEFAULT FALSE,
    had_dinner BOOLEAN DEFAULT FALSE,
    declaration_notes TEXT,
    function_id INT,
    function_group_id INT,
    pl_approved BOOLEAN DEFAULT FALSE,
    pl_approved_at TIMESTAMP NULL,
    pl_approved_by VARCHAR(36),
    finance_approved BOOLEAN DEFAULT FALSE,
    finance_approved_at TIMESTAMP NULL,
    finance_approved_by VARCHAR(36),
    last_edited_by VARCHAR(36),
    last_edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_synced INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_crew (project_id, crew_rentman_id),
    INDEX idx_function_group (function_group_id),
    CONSTRAINT fk_project_crew_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_crew_function_group
        FOREIGN KEY (function_group_id)
        REFERENCES ProjectFunctionGroups(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_project_crew_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- ProjectFunctions table
CREATE TABLE IF NOT EXISTS ProjectFunctions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT NOT NULL,
    project_id INT NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    cost_rate_id INT,
    price_rate_id INT,
    amount INT DEFAULT 1,
    is_offerte BOOLEAN DEFAULT FALSE,
    last_synced INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_rentman_project (rentman_id, project_id),
    INDEX idx_project_function (project_id, rentman_id),
    INDEX idx_is_offerte (is_offerte),
    CONSTRAINT fk_project_functions_project
        FOREIGN KEY (project_id)
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_functions_sync
        FOREIGN KEY (last_synced)
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- ProjectFunctionGroups table
CREATE TABLE IF NOT EXISTS ProjectFunctionGroups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT NOT NULL,
    project_id INT NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    planperiod_start DATETIME,
    planperiod_end DATETIME,
    last_synced INT,
    is_offerte BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_rentman_project (rentman_id, project_id),
    INDEX idx_project_group (project_id, rentman_id),
    CONSTRAINT fk_project_function_groups_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_function_groups_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- HardwareCategories table
CREATE TABLE IF NOT EXISTS MaterialCategories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT UNIQUE NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    parent_id INT,
    path VARCHAR(255),
    is_deleted BOOLEAN DEFAULT FALSE,
    last_synced INT,
    category_type ENUM('RENTAL', 'CUSTOMER') DEFAULT 'RENTAL',
    customer_id INT,
    customer_name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rentman_id (rentman_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_category_type (category_type),
    INDEX idx_customer_id (customer_id),
    CONSTRAINT fk_hardware_categories_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- Materials table
CREATE TABLE IF NOT EXISTS Materials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT UNIQUE NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    folder_id INT NOT NULL,
    code VARCHAR(50),
    name VARCHAR(255),
    type VARCHAR(50),
    price DECIMAL(10,2),
    location_in_warehouse VARCHAR(100),
    last_synced INT,
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rentman_id (rentman_id),
    INDEX idx_folder_id (folder_id),
    CONSTRAINT fk_materials_folder
        FOREIGN KEY (folder_id) 
        REFERENCES HardwareCategories(rentman_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_materials_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- MaterialExemplaren table
CREATE TABLE IF NOT EXISTS MaterialExemplaren (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rentman_id INT UNIQUE NOT NULL,
    material_id INT NOT NULL,
    displayname VARCHAR(255) NOT NULL,
    serial VARCHAR(100),
    qr_code VARCHAR(100),
    active BOOLEAN DEFAULT TRUE,
    purchase_date DATE,
    folder_id INT NOT NULL,
    last_synced INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_rentman_id (rentman_id),
    INDEX idx_material_id (material_id),
    INDEX idx_folder_id (folder_id),
    INDEX idx_serial (serial),
    CONSTRAINT fk_exemplaren_material
        FOREIGN KEY (material_id) 
        REFERENCES Materials(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_exemplaren_folder
        FOREIGN KEY (folder_id) 
        REFERENCES materialcategories(rentman_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_exemplaren_sync
        FOREIGN KEY (last_synced) 
        REFERENCES SyncHistory(id)
        ON DELETE SET NULL
);

-- MaterialExemplarenQrcodes table for storing multiple QR codes
CREATE TABLE IF NOT EXISTS MaterialExemplarenQrcodes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    exemplaar_id INT NOT NULL,
    qr_code VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_exemplaar_id (exemplaar_id),
    INDEX idx_qr_code (qr_code),
    UNIQUE KEY unique_exemplaar_qrcode (exemplaar_id, qr_code),
    CONSTRAINT fk_qrcodes_exemplaar
        FOREIGN KEY (exemplaar_id) 
        REFERENCES MaterialExemplaren(id)
        ON DELETE CASCADE
);

-- Drop the ProjectLocations table if it exists

-- ProjectPreparation table (MUST COME AFTER ProjectLocations)
CREATE TABLE IF NOT EXISTS ProjectPreparation (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    gebied_id INT,
    hoofdlocatie_id INT,
    locatie_id INT,
    coordinaten VARCHAR(100),
    wifi BOOLEAN DEFAULT FALSE,
    utp_info VARCHAR(255),
    uplink_type ENUM('P2P', 'P2MP', 'UTP', 'FIBER', 'VDSL', 'LTE'),
    uplink_vanaf VARCHAR(255),
    pp_poort VARCHAR(100),
    opleverdatum DATE,
    opleverdagdeel ENUM('OCHTEND', 'MIDDAG') DEFAULT 'OCHTEND',
    offline_datum DATE,
    offline_dagdeel ENUM('OCHTEND', 'MIDDAG') DEFAULT 'MIDDAG',
    stroom VARCHAR(255),
    palen_aantal INT,
    overige_productionele_aanvraag TEXT,
    status ENUM('NOG_GOED_TE_KEUREN', 'GOEDGEKEURD') DEFAULT 'NOG_GOED_TE_KEUREN',
    task_id INT,
    OldTaskId INT,
    last_edited_by VARCHAR(36),
    last_edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_opleverdatum (opleverdatum),
    INDEX idx_offline_datum (offline_datum),
    INDEX idx_project_dates (project_id, opleverdatum, offline_datum),
    INDEX idx_task_id (task_id),
    INDEX idx_old_task_id (OldTaskId),
    CONSTRAINT fk_preparation_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_preparation_gebied 
        FOREIGN KEY (gebied_id) 
        REFERENCES LocationAreas(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_preparation_hoofdlocatie 
        FOREIGN KEY (hoofdlocatie_id) 
        REFERENCES LocationMain(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_preparation_locatie 
        FOREIGN KEY (locatie_id) 
        REFERENCES LocationSub(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_preparation_task
        FOREIGN KEY (task_id) 
        REFERENCES ProjectTasks(Id)
        ON DELETE SET NULL
);

-- Location hierarchy tables (updated with project_id)
CREATE TABLE IF NOT EXISTS LocationAreas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_locationareas_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS LocationMain (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    area_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_area_id (area_id),
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_locationmain_area
        FOREIGN KEY (area_id) 
        REFERENCES LocationAreas(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_locationmain_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS LocationSub (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    main_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_main_id (main_id),
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_locationsub_main
        FOREIGN KEY (main_id) 
        REFERENCES LocationMain(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_locationsub_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

-- SSID table for network configurations
CREATE TABLE IF NOT EXISTS SSID (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    authentication ENUM('OPEN', 'PASSWORD') DEFAULT 'PASSWORD',
    password VARCHAR(255),
    vlan_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_name (name),
    CONSTRAINT fk_ssid_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
    CONSTRAINT fk_ssid_vlan
    FOREIGN KEY (vlan_id)
    REFERENCES ProjectEngineerVlans(id)
);

-- Request Location hierarchy tables (updated with project_id)
CREATE TABLE IF NOT EXISTS RequestLocationAreas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_requestlocationareas_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS RequestLocationMain (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    area_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_area_id (area_id),
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_requestlocationmain_area
        FOREIGN KEY (area_id) 
        REFERENCES RequestLocationAreas(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_requestlocationmain_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS RequestLocationSub (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    main_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_main_id (main_id),
    INDEX idx_name (name),
    INDEX idx_project (project_id),
    CONSTRAINT fk_requestlocationsub_main
        FOREIGN KEY (main_id) 
        REFERENCES RequestLocationMain(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_requestlocationsub_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

-- ProjectRequests table (renamed from Aanvraag)
CREATE TABLE IF NOT EXISTS ProjectRequests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    gebied_id INT,
    hoofdlocatie_id INT,
    locatie_id INT,
    coordinaten VARCHAR(100),
    wifi BOOLEAN DEFAULT FALSE,
    utp_info VARCHAR(255),
    uplink_type ENUM('P2P', 'P2MP', 'UTP', 'FIBER', 'VDSL', 'LTE'),
    opleverdatum DATE,
    opleverdagdeel ENUM('OCHTEND', 'MIDDAG') DEFAULT 'OCHTEND',
    offline_datum DATE,
    offline_dagdeel ENUM('OCHTEND', 'MIDDAG') DEFAULT 'MIDDAG',
    status ENUM('NOG_GOED_TE_KEUREN', 'GEWIJZIGD', 'GOEDGEKEURD') DEFAULT 'NOG_GOED_TE_KEUREN',
    preparation_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_opleverdatum (opleverdatum),
    INDEX idx_offline_datum (offline_datum),
    INDEX idx_status (status),
    INDEX idx_preparation_id (preparation_id),
    INDEX idx_project_requests_dates (project_id, opleverdatum, offline_datum),
    CONSTRAINT fk_project_requests_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_requests_gebied 
        FOREIGN KEY (gebied_id) 
        REFERENCES RequestLocationAreas(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_project_requests_hoofdlocatie 
        FOREIGN KEY (hoofdlocatie_id) 
        REFERENCES RequestLocationMain(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_project_requests_locatie 
        FOREIGN KEY (locatie_id) 
        REFERENCES RequestLocationSub(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_project_requests_preparation
        FOREIGN KEY (preparation_id)
        REFERENCES ProjectPreparation(id)
        ON DELETE SET NULL
);

-- Junction table for ProjectRequests and SSID (many-to-many relationship)
CREATE TABLE IF NOT EXISTS ProjectRequestSSID (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_request_id INT NOT NULL,
    ssid_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_request_ssid (project_request_id, ssid_id),
    INDEX idx_project_request_id (project_request_id),
    INDEX idx_ssid_id (ssid_id),
    CONSTRAINT fk_project_request_ssid_request
        FOREIGN KEY (project_request_id) 
        REFERENCES ProjectRequests(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_request_ssid_ssid
        FOREIGN KEY (ssid_id) 
        REFERENCES SSID(id)
        ON DELETE CASCADE
);

-- Create PreparationSSID junction table
CREATE TABLE IF NOT EXISTS PreparationSSID (
    id INT PRIMARY KEY AUTO_INCREMENT,
    preparation_id INT NOT NULL,
    ssid_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_preparation_ssid (preparation_id, ssid_id),
    INDEX idx_preparation_id (preparation_id),
    INDEX idx_ssid_id (ssid_id),
    CONSTRAINT fk_preparation_ssid_preparation
        FOREIGN KEY (preparation_id) 
        REFERENCES ProjectPreparation(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_preparation_ssid_ssid
        FOREIGN KEY (ssid_id) 
        REFERENCES SSID(id)
        ON DELETE CASCADE
);

-- Create ProjectTaskSSID junction table
CREATE TABLE IF NOT EXISTS ProjectTaskSSID (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    ssid_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_task_ssid (task_id, ssid_id),
    INDEX idx_task_id (task_id),
    INDEX idx_ssid_id (ssid_id),
    CONSTRAINT fk_task_ssid_task
        FOREIGN KEY (task_id) 
        REFERENCES ProjectTasks(Id)
        ON DELETE CASCADE,
    CONSTRAINT fk_task_ssid_ssid
        FOREIGN KEY (ssid_id) 
        REFERENCES SSID(id)
        ON DELETE CASCADE
);

-- Add status column to ProjectPreparation table
ALTER TABLE ProjectPreparation
ADD COLUMN IF NOT EXISTS OldTaskId INT NULL AFTER task_id,
ADD INDEX IF NOT EXISTS idx_old_task_id (OldTaskId);

-- Create ProjectPreparationSSID junction table
CREATE TABLE IF NOT EXISTS ProjectPreparationSSID (
    id INT PRIMARY KEY AUTO_INCREMENT,
    preparation_id INT NOT NULL,
    ssid_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_preparation_ssid (preparation_id, ssid_id),
    INDEX idx_preparation_id (preparation_id),
    INDEX idx_ssid_id (ssid_id),
    CONSTRAINT fk_preparation_ssid_preparation
        FOREIGN KEY (preparation_id) 
        REFERENCES ProjectPreparation(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_preparation_ssid_ssid
        FOREIGN KEY (ssid_id) 
        REFERENCES SSID(id)
        ON DELETE CASCADE
);

-- Create ProjectHardware junction table
CREATE TABLE IF NOT EXISTS ProjectHardware (
    id INT PRIMARY KEY AUTO_INCREMENT,
    preparation_id INT NOT NULL,
    project_id INT NOT NULL,
    materials_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_prep_project_material (preparation_id, project_id, materials_id),
    INDEX idx_preparation_id (preparation_id),
    INDEX idx_project_id (project_id),
    INDEX idx_materials_id (materials_id),
    CONSTRAINT fk_projecthardware_preparation
        FOREIGN KEY (preparation_id) 
        REFERENCES ProjectPreparation(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_projecthardware_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_projecthardware_materials
        FOREIGN KEY (materials_id) 
        REFERENCES Materials(id)
        ON DELETE CASCADE
);

-- Create SwitchTemplates table for switch configuration
CREATE TABLE IF NOT EXISTS SwitchTemplates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    material_id INT NOT NULL,
    folder_id INT NOT NULL,
    utp_amount INT DEFAULT 48,
    fiber_amount INT DEFAULT 0,
    xp_amount INT DEFAULT 0,
    utp_order INT DEFAULT 1,
    fiber_order INT DEFAULT 2,
    xp_order INT DEFAULT 3,
    has_fiber BOOLEAN DEFAULT FALSE,
    has_xp BOOLEAN DEFAULT FALSE,
    p1 VARCHAR(100),
    p2 VARCHAR(100),
    p3 VARCHAR(100),
    p4 VARCHAR(100),
    p5 VARCHAR(100),
    p6 VARCHAR(100),
    p7 VARCHAR(100),
    p8 VARCHAR(100),
    p9 VARCHAR(100),
    p10 VARCHAR(100),
    p11 VARCHAR(100),
    p12 VARCHAR(100),
    p13 VARCHAR(100),
    p14 VARCHAR(100),
    p15 VARCHAR(100),
    p16 VARCHAR(100),
    p17 VARCHAR(100),
    p18 VARCHAR(100),
    p19 VARCHAR(100),
    p20 VARCHAR(100),
    p21 VARCHAR(100),
    p22 VARCHAR(100),
    p23 VARCHAR(100),
    p24 VARCHAR(100),
    p25 VARCHAR(100),
    p26 VARCHAR(100),
    p27 VARCHAR(100),
    p28 VARCHAR(100),
    p29 VARCHAR(100),
    p30 VARCHAR(100),
    p31 VARCHAR(100),
    p32 VARCHAR(100),
    p33 VARCHAR(100),
    p34 VARCHAR(100),
    p35 VARCHAR(100),
    p36 VARCHAR(100),
    p37 VARCHAR(100),
    p38 VARCHAR(100),
    p39 VARCHAR(100),
    p40 VARCHAR(100),
    p41 VARCHAR(100),
    p42 VARCHAR(100),
    p43 VARCHAR(100),
    p44 VARCHAR(100),
    p45 VARCHAR(100),
    p46 VARCHAR(100),
    p47 VARCHAR(100),
    p48 VARCHAR(100),
    f1 VARCHAR(100),
    f2 VARCHAR(100),
    f3 VARCHAR(100),
    f4 VARCHAR(100),
    f5 VARCHAR(100),
    f6 VARCHAR(100),
    f7 VARCHAR(100),
    f8 VARCHAR(100),
    f9 VARCHAR(100),
    f10 VARCHAR(100),
    f11 VARCHAR(100),
    f12 VARCHAR(100),
    f13 VARCHAR(100),
    f14 VARCHAR(100),
    f15 VARCHAR(100),
    f16 VARCHAR(100),
    f17 VARCHAR(100),
    f18 VARCHAR(100),
    f19 VARCHAR(100),
    f20 VARCHAR(100),
    f21 VARCHAR(100),
    f22 VARCHAR(100),
    f23 VARCHAR(100),
    f24 VARCHAR(100),
    f25 VARCHAR(100),
    f26 VARCHAR(100),
    f27 VARCHAR(100),
    f28 VARCHAR(100),
    f29 VARCHAR(100),
    f30 VARCHAR(100),
    f31 VARCHAR(100),
    f32 VARCHAR(100),
    f33 VARCHAR(100),
    f34 VARCHAR(100),
    f35 VARCHAR(100),
    f36 VARCHAR(100),
    f37 VARCHAR(100),
    f38 VARCHAR(100),
    f39 VARCHAR(100),
    f40 VARCHAR(100),
    f41 VARCHAR(100),
    f42 VARCHAR(100),
    f43 VARCHAR(100),
    f44 VARCHAR(100),
    f45 VARCHAR(100),
    f46 VARCHAR(100),
    f47 VARCHAR(100),
    f48 VARCHAR(100),
    xp1 VARCHAR(100),
    xp2 VARCHAR(100),
    xp3 VARCHAR(100),
    xp4 VARCHAR(100),
    xp5 VARCHAR(100),
    xp6 VARCHAR(100),
    xp7 VARCHAR(100),
    xp8 VARCHAR(100),
    xp9 VARCHAR(100),
    xp10 VARCHAR(100),
    xp11 VARCHAR(100),
    xp12 VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_material_id (material_id),
    INDEX idx_folder_id (folder_id),
    CONSTRAINT fk_switchtemplate_material
        FOREIGN KEY (material_id) 
        REFERENCES Materials(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_switchtemplate_folder
        FOREIGN KEY (folder_id) 
        REFERENCES MaterialCategories(rentman_id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ChecklistTemplates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_name (template_name)
);

CREATE TABLE IF NOT EXISTS ChecklistTemplateItems (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    description TEXT,
    order_index INT NOT NULL DEFAULT 0,
    category_id INT NULL,
    parent_id INT NULL,
    is_subtask BOOLEAN DEFAULT FALSE,
    depth_level INT DEFAULT 0,
    subtask_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_id (template_id),
    INDEX idx_category_id (category_id),
    INDEX idx_order (order_index),
    INDEX idx_parent_id (parent_id),
    INDEX idx_is_subtask (is_subtask),
    CONSTRAINT fk_template_items_template
        FOREIGN KEY (template_id) 
        REFERENCES ChecklistTemplates(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_template_items_parent
        FOREIGN KEY (parent_id)
        REFERENCES ChecklistTemplateItems(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_template_items_category
        FOREIGN KEY (category_id)
        REFERENCES ChecklistCategories(id)
        ON DELETE SET NULL
);

-- Create Categories table for checklist templates
CREATE TABLE IF NOT EXISTS ChecklistCategories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    color VARCHAR(50),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Create PreparationChecklist table
CREATE TABLE IF NOT EXISTS PreparationChecklist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    description TEXT,
    is_completed BOOLEAN DEFAULT FALSE,
    completed_by VARCHAR(36),
    completed_at TIMESTAMP NULL,
    order_index INT NOT NULL DEFAULT 0,
    category_id INT NULL,
    parent_id INT NULL,
    is_subtask BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_is_completed (is_completed),
    INDEX idx_category_id (category_id),
    INDEX idx_order (order_index),
    INDEX idx_parent_id (parent_id),
    INDEX idx_is_subtask (is_subtask),
    CONSTRAINT fk_preparation_checklist_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_preparation_checklist_parent
        FOREIGN KEY (parent_id)
        REFERENCES PreparationChecklist(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_preparation_checklist_completed_by
        FOREIGN KEY (completed_by)
        REFERENCES Users(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_preparation_checklist_category
        FOREIGN KEY (category_id)
        REFERENCES ChecklistCategories(id)
        ON DELETE SET NULL
);

-- Create Tasks table
CREATE TABLE IF NOT EXISTS ProjectTasks (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    ProjectId INT NOT NULL,
    GebiedId INT,
    HoofdlocatieId INT,
    LocatieId INT,
    Coordinaten TEXT,
    UtpInfo TEXT,
    UplinkType ENUM('P2P', 'P2MP', 'UTP', 'FIBER', 'VDSL', 'LTE'),
    UplinkVanaf VARCHAR(255),
    PpPoort VARCHAR(255),
    BouwdatumDate DATE,
    OpleverdatumDate DATE,
    OpleverdagdeelOplever ENUM('OCHTEND', 'MIDDAG') DEFAULT 'OCHTEND',
    OfflineDatum DATE,
    OfflineDagdeel ENUM('OCHTEND', 'MIDDAG') DEFAULT 'MIDDAG',
    TeamNumber INT,
    Engineer_Status VARCHAR(255),
    OpbouwStatus ENUM('NOG_NIET_BEGONNEN', 'IN_UITVOERING', 'DEELS_AFGEROND', 'AFGEROND') DEFAULT 'NOG_NIET_BEGONNEN',
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CrewComment TEXT,
    PreparationId INT,
    TaskNumber INT,
    FOREIGN KEY (ProjectId) REFERENCES Projects(id),
    FOREIGN KEY (GebiedId) REFERENCES LocationAreas(id),
    FOREIGN KEY (HoofdlocatieId) REFERENCES LocationMain(id),
    FOREIGN KEY (LocatieId) REFERENCES LocationSub(id)
);

-- Replace the ProjectSwitchTasks table with a normalized version
CREATE TABLE IF NOT EXISTS ProjectSwitchTasks (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    TaskId INT NOT NULL,
    MaterialId INT NOT NULL,
    ExemplaarId INT,
    FolderId INT NOT NULL,
    UtpAmount INT DEFAULT 48,
    FiberAmount INT DEFAULT 0,
    XpAmount INT DEFAULT 0,
    UtpOrder INT DEFAULT 1,
    FiberOrder INT DEFAULT 2,
    XpOrder INT DEFAULT 3,
    HasFiber BOOLEAN DEFAULT FALSE,
    HasXp BOOLEAN DEFAULT FALSE,
    TemplateId INT,
    Eng_FullyDone BOOLEAN DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX Idx_TaskId (TaskId),
    INDEX Idx_MaterialId (MaterialId),
    INDEX Idx_FolderId (FolderId),
    INDEX Idx_TemplateId (TemplateId),
    INDEX Idx_ExemplaarId (ExemplaarId),
    CONSTRAINT Fk_SwitchTasks_Task
        FOREIGN KEY (TaskId) 
        REFERENCES ProjectTasks(Id)
        ON DELETE CASCADE,
    CONSTRAINT Fk_SwitchTasks_Material
        FOREIGN KEY (MaterialId) 
        REFERENCES Materials(id)
        ON DELETE CASCADE,
    CONSTRAINT Fk_SwitchTasks_Exemplaar
        FOREIGN KEY (ExemplaarId) 
        REFERENCES MaterialExemplaren(id)
        ON DELETE SET NULL,
    CONSTRAINT Fk_SwitchTasks_Folder
        FOREIGN KEY (FolderId) 
        REFERENCES MaterialCategories(rentman_id)
        ON DELETE CASCADE,
    CONSTRAINT Fk_SwitchTasks_Template
        FOREIGN KEY (TemplateId) 
        REFERENCES SwitchTemplates(id)
        ON DELETE SET NULL
);

-- Create new normalized table for port configurations
CREATE TABLE IF NOT EXISTS ProjectSwitchPortConfigurations (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    SwitchTaskId INT NOT NULL,
    PortType ENUM('UTP', 'FIBER', 'XP') NOT NULL,
    PortNumber INT NOT NULL,
    PortValue TEXT,
    PortComment TEXT,
    LastValue TEXT,
    IsChanged BOOLEAN DEFAULT FALSE,
    EngDone BOOLEAN DEFAULT FALSE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY Unique_Switch_Port (SwitchTaskId, PortType, PortNumber),
    INDEX Idx_SwitchTaskId (SwitchTaskId),
    INDEX Idx_PortType_Number (PortType, PortNumber),
    INDEX Idx_EngDone (EngDone),
    CONSTRAINT Fk_PortConfig_SwitchTask
        FOREIGN KEY (SwitchTaskId) 
        REFERENCES ProjectSwitchTasks(Id)
        ON DELETE CASCADE
);

-- Create ProjectTaskMaterial table to track materials (other than switches) needed for tasks
CREATE TABLE IF NOT EXISTS ProjectTaskMaterial (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    TaskId INT NOT NULL,
    MaterialId INT NOT NULL,
    ExemplaarId INT,
    Quantity INT NOT NULL DEFAULT 1,
    Opmerking TEXT,
    Status ENUM('NODIG', 'AANGEVRAAGD', 'ONTVANGEN', 'GEINSTALLEERD') DEFAULT 'NODIG',
    IsChanged BOOLEAN DEFAULT FALSE,
    LastValue TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX Idx_TaskId (TaskId),
    INDEX Idx_MaterialId (MaterialId),
    INDEX Idx_ExemplaarId (ExemplaarId),
    INDEX Idx_Status (Status),
    CONSTRAINT Fk_TaskMaterial_Task
        FOREIGN KEY (TaskId) 
        REFERENCES ProjectTasks(Id)
        ON DELETE CASCADE,
    CONSTRAINT Fk_TaskMaterial_Material
        FOREIGN KEY (MaterialId) 
        REFERENCES Materials(id)
        ON DELETE CASCADE,
    CONSTRAINT Fk_TaskMaterial_Exemplaar
        FOREIGN KEY (ExemplaarId) 
        REFERENCES MaterialExemplaren(id)
        ON DELETE SET NULL
);

-- ProjectEngineerSettings table
CREATE TABLE IF NOT EXISTS ProjectEngineerSettings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    mobset VARCHAR(255),
    zabbix_hostgroup_id INT,
    zabbix_groupname VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_project_settings (project_id),
    CONSTRAINT fk_project_engineer_settings_project
        FOREIGN KEY (project_id)
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

-- ProjectEngineerVlans table
CREATE TABLE IF NOT EXISTS ProjectEngineerVlans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    vlan_number INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    subnet VARCHAR(255) NOT NULL,
    opmerking TEXT,
    crewvisible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_vlan_number (vlan_number),
    UNIQUE KEY unique_project_vlan (project_id, vlan_number),
    CONSTRAINT fk_project_engineer_vlans_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE
);

-- Create VlanTemplates table
CREATE TABLE IF NOT EXISTS VlanTemplates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_name (template_name)
);

-- Create VlanTemplateItems table
CREATE TABLE IF NOT EXISTS VlanTemplateItems (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_id INT NOT NULL,
    vlan_number INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    subnet VARCHAR(255) NOT NULL,
    opmerking TEXT,
    crewvisible BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_id (template_id),
    INDEX idx_vlan_number (vlan_number),
    UNIQUE KEY unique_template_vlan (template_id, vlan_number),
    CONSTRAINT fk_vlan_template_items_template
        FOREIGN KEY (template_id) 
        REFERENCES VlanTemplates(id)
        ON DELETE CASCADE
);

-- Create ProjectImages table
CREATE TABLE IF NOT EXISTS ProjectImages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    gebied_id INT,
    url VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    INDEX idx_gebied_id (gebied_id),
    CONSTRAINT fk_project_images_project
        FOREIGN KEY (project_id) 
        REFERENCES Projects(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_project_images_gebied
        FOREIGN KEY (gebied_id) 
        REFERENCES LocationAreas(id)
        ON DELETE SET NULL
);

-- SwitchFolderIDs table to store switch folder IDs
CREATE TABLE IF NOT EXISTS SwitchFolderIDs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    folder_id INT NOT NULL UNIQUE,
    folder_name VARCHAR(255),
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_folder_id (folder_id),
    INDEX idx_is_active (is_active)
);

-- CustomerMaterials table to store customer-specific material folders with switch IDs
CREATE TABLE IF NOT EXISTS CustomerMaterials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    folder_id INT NOT NULL,
    folder_name VARCHAR(255),
    switch_folder_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer_id (customer_id),
    INDEX idx_folder_id (folder_id),
    INDEX idx_switch_folder_id (switch_folder_id),
    INDEX idx_is_active (is_active),
    UNIQUE KEY unique_customer_folder (customer_id, folder_id),
    CONSTRAINT fk_customer_materials_switch_folder
        FOREIGN KEY (switch_folder_id)
        REFERENCES SwitchFolderIDs(folder_id)
        ON DELETE SET NULL
);
