-- Domestat.com — Core Schema
-- Unified multi-country (US / CA / UK) real estate listings platform
-- Engine: InnoDB, utf8mb4 throughout

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- ADMIN USERS
-- ============================================================
CREATE TABLE admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','editor') NOT NULL DEFAULT 'editor',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- AGENTS
-- ============================================================
CREATE TABLE agents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(170) NOT NULL UNIQUE,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,
    country ENUM('US','CA','UK') NOT NULL,
    license_number VARCHAR(60) NULL,
    photo_url VARCHAR(500) NULL,
    bio TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agents_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LISTINGS (unified core table)
-- ============================================================
CREATE TABLE listings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- provenance
    source_type ENUM('manual','mls_idx','crea_ddf','uk_feed') NOT NULL DEFAULT 'manual',
    source_listing_id VARCHAR(100) NULL,           -- external ID from feed, NULL for manual
    is_manually_overridden TINYINT(1) NOT NULL DEFAULT 0,  -- freezes this row from sync writes
    last_synced_at DATETIME NULL,

    -- classification
    country ENUM('US','CA','UK') NOT NULL,
    status ENUM('draft','active','pending','sold','withdrawn') NOT NULL DEFAULT 'draft',
    property_type ENUM('house','condo','apartment','townhouse','land','commercial','other') NOT NULL DEFAULT 'house',
    listing_kind ENUM('sale','rent') NOT NULL DEFAULT 'sale',

    -- pricing
    list_price DECIMAL(14,2) NOT NULL,
    currency CHAR(3) NOT NULL,                     -- USD / CAD / GBP
    price_period ENUM('one_time','month','week') NOT NULL DEFAULT 'one_time',

    -- facts
    bedrooms SMALLINT UNSIGNED NULL,
    bathrooms DECIMAL(3,1) UNSIGNED NULL,          -- allows 2.5
    reception_rooms SMALLINT UNSIGNED NULL,        -- UK convention
    area_sqft DECIMAL(10,2) NULL,
    area_sqm DECIMAL(10,2) NULL,
    lot_size_sqft DECIMAL(12,2) NULL,
    year_built SMALLINT UNSIGNED NULL,
    epc_rating CHAR(1) NULL,                       -- UK-only: A-G

    -- content
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_listings_search (country, status, list_price),
    INDEX idx_listings_source (source_type, source_listing_id),
    INDEX idx_listings_type (property_type, listing_kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- ADDRESSES (1:1 with listings — split out for clean search/SEO)
-- ============================================================
CREATE TABLE addresses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    listing_id INT UNSIGNED NOT NULL,
    street_line1 VARCHAR(255) NOT NULL,
    street_line2 VARCHAR(255) NULL,
    city VARCHAR(120) NOT NULL,
    region VARCHAR(120) NULL,                      -- state / province / county
    postal_code VARCHAR(20) NULL,
    country ENUM('US','CA','UK') NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    slug VARCHAR(300) NOT NULL,                    -- SEO-friendly URL segment

    CONSTRAINT fk_addresses_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_listing_address (listing_id),
    INDEX idx_addresses_location (country, region, city),
    INDEX idx_addresses_slug (slug(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LISTING MEDIA
-- ============================================================
CREATE TABLE listing_media (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    listing_id INT UNSIGNED NOT NULL,
    url VARCHAR(500) NOT NULL,
    type ENUM('photo','video','floorplan','virtual_tour') NOT NULL DEFAULT 'photo',
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    alt_text VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_media_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
    INDEX idx_media_listing (listing_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LISTING <-> AGENTS (many-to-many, co-listings happen)
-- ============================================================
CREATE TABLE listing_agents (
    listing_id INT UNSIGNED NOT NULL,
    agent_id INT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 1,

    PRIMARY KEY (listing_id, agent_id),
    CONSTRAINT fk_la_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE CASCADE,
    CONSTRAINT fk_la_agent FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LEADS (contact form + consultation requests)
-- ============================================================
CREATE TABLE leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    listing_id INT UNSIGNED NULL,                  -- nullable: general inquiry has no listing
    type ENUM('contact_form','consultation_request') NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,
    message TEXT NULL,
    preferred_contact_method ENUM('email','phone','either') NOT NULL DEFAULT 'either',

    -- consultation-specific (nullable, only used when type = consultation_request)
    consultation_intent ENUM('buying','selling','investing','relocating') NULL,
    consultation_country ENUM('US','CA','UK') NULL,
    consultation_budget VARCHAR(60) NULL,
    consultation_timeframe VARCHAR(60) NULL,

    status ENUM('new','contacted','closed') NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_leads_listing FOREIGN KEY (listing_id) REFERENCES listings(id) ON DELETE SET NULL,
    INDEX idx_leads_status (type, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SAVED SEARCHES (v2 feature, schema ready now)
-- ============================================================
CREATE TABLE saved_searches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(190) NOT NULL,
    country ENUM('US','CA','UK') NOT NULL,
    filters_json JSON NOT NULL,
    frequency ENUM('instant','daily','weekly') NOT NULL DEFAULT 'daily',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_saved_searches_active (is_active, frequency)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- FEED SYNC LOG (observability for the cron adapters)
-- ============================================================
CREATE TABLE feed_sync_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_type ENUM('mls_idx','crea_ddf','uk_feed') NOT NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    listings_created INT UNSIGNED NOT NULL DEFAULT 0,
    listings_updated INT UNSIGNED NOT NULL DEFAULT 0,
    listings_withdrawn INT UNSIGNED NOT NULL DEFAULT 0,
    listings_skipped_override INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('running','success','failed') NOT NULL DEFAULT 'running',
    error_message TEXT NULL,

    INDEX idx_sync_log_source (source_type, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- AUDIT LOG (who changed what listing, when)
-- ============================================================
CREATE TABLE audit_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NULL,
    entity_type VARCHAR(50) NOT NULL,              -- 'listing', 'agent', etc.
    entity_id INT UNSIGNED NOT NULL,
    action VARCHAR(50) NOT NULL,                   -- 'created', 'updated', 'status_changed', etc.
    details_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_audit_admin FOREIGN KEY (admin_user_id) REFERENCES admin_users(id) ON DELETE SET NULL,
    INDEX idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;