api/schema.sql

-- InnSight-AI Database Schema
-- Run this script to initialize the database tables

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(512) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    totp_secret VARCHAR(64),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

-- Hotels table
CREATE TABLE IF NOT EXISTS hotels (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    booking_url TEXT,
    website_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Price comparisons history table
CREATE TABLE IF NOT EXISTS price_comparisons (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    hotel_ids INTEGER[] NOT NULL,
    check_in DATE NOT NULL,
    check_out DATE NOT NULL,
    occupancy VARCHAR(50),
    results JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_price_comparisons_user ON price_comparisons(user_id);
CREATE INDEX IF NOT EXISTS idx_price_comparisons_date ON price_comparisons(created_at);

-- Seed admin users (update passwords and secrets in production!)
-- Note: Run these manually with proper hashed passwords

-- Example admin insert (DO NOT USE IN PRODUCTION - passwords should be hashed):
-- INSERT INTO users (email, password_hash, full_name, is_admin, totp_secret)
-- VALUES ('[email protected]', '<hashed_password>', 'Admin User', true, '<totp_secret>');

-- Example hotels insert:
INSERT INTO hotels (name, booking_url, website_url) VALUES
    ('צובה', 'https://booking.com/hotel/tzuba', 'https://tzuba.co.il'),
    ('רמת רחל', 'https://booking.com/hotel/ramat-rachel', 'https://ramatrachel.co.il'),
    ('יד השמונה', 'https://booking.com/hotel/yad-hashmona', 'https://yadhashmona.co.il'),
    ('יערים', 'https://booking.com/hotel/yearim', 'https://yearim.co.il'),
    ('נווה אילן', 'https://booking.com/hotel/neve-ilan', 'https://neveilan.co.il')
ON CONFLICT DO NOTHING;