-- Create users table for WhatsApp AI Agent
CREATE TABLE IF NOT EXISTS users (
    wa_id TEXT PRIMARY KEY,
    name TEXT NOT NULL DEFAULT 'Unknown',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create chat_sessions table
CREATE TABLE IF NOT EXISTS chat_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    wa_id TEXT NOT NULL REFERENCES users(wa_id) ON DELETE CASCADE,
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_activity TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ended BOOLEAN DEFAULT FALSE
);

-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
    wa_id TEXT NOT NULL REFERENCES users(wa_id) ON DELETE CASCADE,
    wamid TEXT NOT NULL, -- Meta message ID
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant')),
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create user_personas table for user preferences and context
CREATE TABLE IF NOT EXISTS user_personas (
    wa_id TEXT PRIMARY KEY REFERENCES users(wa_id) ON DELETE CASCADE,
    language TEXT,
    tone TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create user_intents table for session-specific property search intent
CREATE TABLE IF NOT EXISTS user_intents (
    session_id UUID PRIMARY KEY REFERENCES chat_sessions(id) ON DELETE CASCADE,
    wa_id TEXT NOT NULL REFERENCES users(wa_id) ON DELETE CASCADE,
    location_preference TEXT,
    budget INTEGER,
    size_preference_sqm INTEGER,
    must_have TEXT[],
    transaction_type TEXT CHECK (transaction_type IN ('rent', 'buy')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Migration: Add transaction_type column to existing user_intents table (if it doesn't exist)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'user_intents' AND column_name = 'transaction_type') THEN
        ALTER TABLE user_intents ADD COLUMN transaction_type TEXT CHECK (transaction_type IN ('rent', 'buy'));
    END IF;
END $$;

-- Migration: Add tags column to existing properties table (if it doesn't exist)
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'properties' AND column_name = 'tags') THEN
        ALTER TABLE properties ADD COLUMN tags TEXT[];
    END IF;
END $$;

-- Create properties table for property listings
CREATE TABLE IF NOT EXISTS properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    description TEXT,
    location TEXT NOT NULL,
    city TEXT NOT NULL,
    address TEXT,
    size_sqm INTEGER,
    price INTEGER,
    price_type TEXT DEFAULT 'monthly',
    listing_url TEXT,
    images TEXT[],
    features TEXT[],
    tags TEXT[],
    floorplan_pdf TEXT,
    video_url TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_users_updated_at ON users(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_wa_id ON chat_sessions(wa_id);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_last_activity ON chat_sessions(last_activity DESC);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_ended ON chat_sessions(ended);
CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id);
CREATE INDEX IF NOT EXISTS idx_messages_wa_id ON messages(wa_id);
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_wamid ON messages(wamid);
CREATE INDEX IF NOT EXISTS idx_user_personas_wa_id ON user_personas(wa_id);
CREATE INDEX IF NOT EXISTS idx_user_personas_updated_at ON user_personas(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_user_intents_session_id ON user_intents(session_id);
CREATE INDEX IF NOT EXISTS idx_user_intents_wa_id ON user_intents(wa_id);
CREATE INDEX IF NOT EXISTS idx_user_intents_updated_at ON user_intents(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_properties_is_active ON properties(is_active);
CREATE INDEX IF NOT EXISTS idx_properties_city ON properties(city);
CREATE INDEX IF NOT EXISTS idx_properties_location ON properties(location);
CREATE INDEX IF NOT EXISTS idx_properties_address ON properties(address);
CREATE INDEX IF NOT EXISTS idx_properties_price ON properties(price);
CREATE INDEX IF NOT EXISTS idx_properties_size_sqm ON properties(size_sqm);
CREATE INDEX IF NOT EXISTS idx_properties_is_featured ON properties(is_featured);
CREATE INDEX IF NOT EXISTS idx_properties_created_at ON properties(created_at DESC);

-- Enable Row Level Security (RLS) - optional but recommended
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE chat_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_personas ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_intents ENABLE ROW LEVEL SECURITY;
ALTER TABLE properties ENABLE ROW LEVEL SECURITY;

-- Create policies to allow all operations (you can restrict this based on your needs)
CREATE POLICY "Allow all operations on users" ON users
    FOR ALL USING (true);

CREATE POLICY "Allow all operations on chat_sessions" ON chat_sessions
    FOR ALL USING (true);

CREATE POLICY "Allow all operations on messages" ON messages
    FOR ALL USING (true);

CREATE POLICY "Allow all operations on user_personas" ON user_personas
    FOR ALL USING (true);

CREATE POLICY "Allow all operations on user_intents" ON user_intents
    FOR ALL USING (true);

CREATE POLICY "Allow all operations on properties" ON properties
    FOR ALL USING (true);

-- Optional: Create a function to automatically update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to automatically update updated_at for users
CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Create trigger to automatically update last_activity for chat_sessions
CREATE OR REPLACE FUNCTION update_last_activity_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_activity = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_chat_sessions_last_activity 
    BEFORE UPDATE ON chat_sessions 
    FOR EACH ROW 
    EXECUTE FUNCTION update_last_activity_column();

-- Create trigger to automatically update updated_at for user_personas
CREATE TRIGGER update_user_personas_updated_at 
    BEFORE UPDATE ON user_personas 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Create trigger to automatically update updated_at for user_intents
CREATE TRIGGER update_user_intents_updated_at 
    BEFORE UPDATE ON user_intents 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Create trigger to automatically update updated_at for properties
CREATE TRIGGER update_properties_updated_at 
    BEFORE UPDATE ON properties 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Insert some sample properties for testing
INSERT INTO properties (title, description, location, city, address, size_sqm, price, price_type, listing_url, images, features, tags, is_active, is_featured) VALUES
(
    '2-3 person office space in Edenvale',
    'Modern office space perfect for small teams',
    'Edenvale',
    'Johannesburg',
    'Greenstone Shopping Centre, 1 Emerald Boulevard, Modderfontein, 1609, South Africa',
    40,
    15000,
    'lease',
    'https://example.com/office-edenvale',
    ARRAY['https://example.com/image1.jpg', 'https://example.com/image2.jpg'],
    ARRAY['3 phase power', 'Parking', 'Kitchen', 'Meeting room'],
    ARRAY['Security', 'Air conditioning', 'High-speed internet', 'Furnished', 'Near transport'],
    TRUE,
    TRUE
),
(
    'Warehouse space in Sandton',
    'Large warehouse space with loading dock',
    'Sandton',
    'Johannesburg',
    'Sandton City Shopping Centre, 83 Rivonia Rd, Sandhurst, Sandton, 2196, South Africa',
    200,
    25000,
    'lease',
    'https://example.com/warehouse-sandton',
    ARRAY['https://example.com/warehouse1.jpg'],
    ARRAY['Loading dock', '3 phase power', 'Security', '24/7 access'],
    ARRAY['CCTV', 'Alarm system', 'Fire suppression', 'Truck access', 'Industrial area'],
    TRUE,
    FALSE
),
(
    'Small office in Cape Town CBD',
    'Cozy office space in the heart of Cape Town',
    'CBD',
    'Cape Town',
    'V&A Waterfront, Dock Rd, Cape Town, 8001, South Africa',
    30,
    12000,
    'lease',
    'https://example.com/office-cape-town',
    ARRAY['https://example.com/cape-town1.jpg'],
    ARRAY['WiFi', 'Reception', 'Lift'],
    ARRAY['Security', 'Ocean view', 'Restaurant nearby', 'Public transport', 'Prestigious address'],
    TRUE,
    FALSE
);

-- Add some sample sale properties for testing
INSERT INTO properties (title, description, location, city, address, size_sqm, price, price_type, listing_url, images, features, is_active, is_featured) VALUES
(
    'Office Building for Sale in Sandton',
    'Modern office building with excellent investment potential',
    'Sandton',
    'Johannesburg', 
    '1 Sandton Drive, Sandton, 2196, South Africa',
    500,
    2500000,
    'sale',
    'https://example.com/office-building-sale',
    ARRAY['https://example.com/building1.jpg', 'https://example.com/building2.jpg'],
    ARRAY['Elevator', 'Parking', 'Security', 'Reception'],
    TRUE,
    TRUE
),
(
    'Warehouse for Sale in Midrand',
    'Industrial warehouse with great location for logistics',
    'Midrand',
    'Johannesburg',
    'New Road, Midrand, 1685, South Africa', 
    1000,
    3200000,
    'sale',
    'https://example.com/warehouse-sale',
    ARRAY['https://example.com/warehouse-sale1.jpg'],
    ARRAY['Loading dock', 'High ceiling', '3 phase power', 'Truck access'],
    TRUE,
    FALSE
),
(
    'Commercial Property for Sale in Cape Town',
    'Prime commercial property in Cape Town CBD',
    'CBD',
    'Cape Town',
    'Long Street, Cape Town, 8001, South Africa',
    300,
    1800000,
    'sale', 
    'https://example.com/commercial-sale-ct',
    ARRAY['https://example.com/commercial1.jpg'],
    ARRAY['Street frontage', 'High foot traffic', 'Parking'],
    TRUE,
    FALSE
);