-- Esquema para MySQL (cPanel)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS properties (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    type VARCHAR(50) NOT NULL, -- Casa, Departamento, Terreno, Bodega, Local, Oficina
    operation VARCHAR(20) NOT NULL, -- Venta, Renta
    price DECIMAL(14, 2) NOT NULL,
    currency VARCHAR(5) DEFAULT 'MXN',
    location_state VARCHAR(80) NOT NULL,
    location_city VARCHAR(100) NOT NULL,
    location_zone VARCHAR(120) NOT NULL,
    address_reference TEXT NULL,
    bedrooms INT DEFAULT 0,
    bathrooms DECIMAL(3, 1) DEFAULT 0,
    parking_spots INT DEFAULT 0,
    land_area DECIMAL(10, 2) DEFAULT 0, -- m2 de terreno
    construction_area DECIMAL(10, 2) DEFAULT 0, -- m2 de construccion
    description TEXT NOT NULL,
    legal_status VARCHAR(150) DEFAULT 'Escriturado y Libre de Gravamen', -- Enfoque diferenciador
    featured TINYINT(1) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'Disponible', -- Disponible, En Trato, Vendido
    main_image VARCHAR(255) NULL,
    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;

CREATE TABLE IF NOT EXISTS property_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inquiries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    property_id INT NULL,
    full_name VARCHAR(120) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(120) NOT NULL,
    message TEXT NOT NULL,
    interest_type VARCHAR(50) DEFAULT 'Interés en Propiedad', -- Legal, Compra, Venta, Renta
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;