CREATE DATABASE IF NOT EXISTS real_estate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE real_estate;

CREATE TABLE IF NOT EXISTS leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(40) NOT NULL,
    location VARCHAR(160) NOT NULL,
    remarks TEXT NOT NULL,
    category VARCHAR(50) NOT NULL,
    requirement_type VARCHAR(150) NOT NULL,
    deal_side VARCHAR(20) NOT NULL,
    lead_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS lead_media (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    media_type VARCHAR(20) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_url VARCHAR(255) NOT NULL,
    size_label VARCHAR(50) DEFAULT '',
    dimension_label VARCHAR(80) DEFAULT '',
    is_compressed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_media_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE
);