CREATE DATABASE IF NOT EXISTS flooring_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE flooring_db;

CREATE TABLE IF NOT EXISTS rooms (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id VARCHAR(100) NOT NULL DEFAULT '',
    project_name VARCHAR(255) NOT NULL,
    room_name VARCHAR(255) NOT NULL,
    box_qty DECIMAL(10,2) NOT NULL DEFAULT 0,
    price_received DECIMAL(10,2) NOT NULL DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_id (project_id),
    UNIQUE INDEX idx_project_room (project_name, room_name)
);

CREATE TABLE IF NOT EXISTS zoho_leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zoho_lead_id VARCHAR(100) NOT NULL DEFAULT '',
    measure_square_project_id VARCHAR(100) NOT NULL,
    project_name VARCHAR(255) NOT NULL,
    lead_name VARCHAR(255) NOT NULL DEFAULT '',
    company VARCHAR(255) NOT NULL DEFAULT '',
    email VARCHAR(255) NOT NULL DEFAULT '',
    phone VARCHAR(100) NOT NULL DEFAULT '',
    property_address TEXT,
    city VARCHAR(100) NOT NULL DEFAULT '',
    state VARCHAR(100) NOT NULL DEFAULT '',
    zip VARCHAR(50) NOT NULL DEFAULT '',
    property_type VARCHAR(100) NOT NULL DEFAULT '',
    area_size VARCHAR(100) NOT NULL DEFAULT '',
    scope_of_work TEXT,
    notes TEXT,
    attachments_json LONGTEXT,
    raw_payload_json LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_zoho_lead_id (zoho_lead_id),
    INDEX idx_ms_project_id (measure_square_project_id)
);