#!/usr/bin/env python3
"""
Database schema for FarmMatch - Single source of truth
"""

import sqlite3
from datetime import datetime
from pathlib import Path

DATABASE_PATH = Path(__file__).parent / 'farmmatch.db'

SCHEMA = """
-- Properties table (main data)
CREATE TABLE IF NOT EXISTS properties (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT UNIQUE NOT NULL,
    title TEXT,
    summary TEXT,
    description TEXT,
    price TEXT,
    location TEXT,
    breadcrumb TEXT,
    status TEXT DEFAULT 'Active',

    -- Geocoding
    lat REAL,
    lon REAL,
    geocoding_confidence TEXT,
    geocoded_from TEXT,
    geocoded_at TIMESTAMP,

    -- Analysis
    gpt_score REAL,
    gpt_reasoning TEXT,
    custom_score REAL,
    custom_reasoning TEXT,
    final_score REAL,
    risk_profile TEXT,

    -- Metadata
    scraped_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_checked TIMESTAMP,

    -- Constraints
    CHECK (lat IS NULL OR (lat >= -90 AND lat <= 90)),
    CHECK (lon IS NULL OR (lon >= -180 AND lon <= 180)),
    CHECK (gpt_score IS NULL OR (gpt_score >= 0 AND gpt_score <= 5)),
    CHECK (custom_score IS NULL OR (custom_score >= 0 AND custom_score <= 5)),
    CHECK (final_score IS NULL OR (final_score >= 0 AND final_score <= 5)),
    CHECK (status IN ('Active', 'Removed', 'Pending', 'Sold'))
);

-- Criteria scores (detailed breakdown)
CREATE TABLE IF NOT EXISTS criteria_scores (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    criterion TEXT NOT NULL,
    score REAL NOT NULL,
    weight REAL DEFAULT 1.0,
    reasoning TEXT,
    evaluated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    CHECK (score >= 1 AND score <= 5),
    UNIQUE(property_id, criterion)
);

-- Geocoding history (audit trail)
CREATE TABLE IF NOT EXISTS geocoding_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    confidence TEXT,
    source TEXT,
    geocoded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

-- Analysis history (audit trail)
CREATE TABLE IF NOT EXISTS analysis_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    property_id INTEGER NOT NULL,
    analysis_type TEXT NOT NULL,
    score REAL,
    reasoning TEXT,
    analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    CHECK (analysis_type IN ('gpt', 'custom', 'final'))
);

-- System metadata (pipeline state)
CREATE TABLE IF NOT EXISTS system_metadata (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_properties_status ON properties(status);
CREATE INDEX IF NOT EXISTS idx_properties_url ON properties(url);
CREATE INDEX IF NOT EXISTS idx_properties_location ON properties(lat, lon);
CREATE INDEX IF NOT EXISTS idx_criteria_property ON criteria_scores(property_id);
CREATE INDEX IF NOT EXISTS idx_geocoding_property ON geocoding_history(property_id);
CREATE INDEX IF NOT EXISTS idx_analysis_property ON analysis_history(property_id);
"""

def create_database(db_path=DATABASE_PATH):
    """Create the database with schema"""
    print(f"Creating database at: {db_path}")

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    # Execute schema
    cursor.executescript(SCHEMA)

    # Add initial metadata
    cursor.execute("""
        INSERT OR REPLACE INTO system_metadata (key, value, updated_at)
        VALUES ('schema_version', '1.0', CURRENT_TIMESTAMP)
    """)

    cursor.execute("""
        INSERT OR REPLACE INTO system_metadata (key, value, updated_at)
        VALUES ('created_at', ?, CURRENT_TIMESTAMP)
    """, (datetime.now().isoformat(),))

    conn.commit()
    conn.close()

    print(f"✅ Database created successfully")

def get_connection(db_path=DATABASE_PATH):
    """Get a database connection"""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row  # Access columns by name
    return conn

if __name__ == '__main__':
    create_database()

    # Verify tables
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()

    print(f"\n📊 Created tables:")
    for table in tables:
        cursor.execute(f"SELECT COUNT(*) as count FROM {table[0]}")
        count = cursor.fetchone()[0]
        print(f"  - {table[0]}: {count} rows")

    conn.close()