-- Create Database
CREATE DATABASE IF NOT EXISTS takeoff_system;
USE takeoff_system;

-- Plans Table
CREATE TABLE IF NOT EXISTS plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_size INT NOT NULL,
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Takeoffs Table
CREATE TABLE IF NOT EXISTS takeoffs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plan_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    takeoff_data LONGTEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE,
    INDEX idx_plan_id (plan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Measurements Table
CREATE TABLE IF NOT EXISTS measurements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    takeoff_id INT NOT NULL,
    type ENUM('area', 'linear', 'point') NOT NULL,
    value DECIMAL(10, 2) NOT NULL,
    unit VARCHAR(50),
    coordinates LONGTEXT,
    color VARCHAR(7),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (takeoff_id) REFERENCES takeoffs(id) ON DELETE CASCADE,
    INDEX idx_takeoff_id (takeoff_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create indexes for better query performance
CREATE INDEX idx_plan_upload_date ON plans(upload_date);
CREATE INDEX idx_takeoff_created ON takeoffs(created_at);
