-- Add new columns to markets and adventures tables
-- This script adds: address, city, country_id, latitude, longitude, phone, email, website, updated_at

-- Add columns to markets table
ALTER TABLE public.markets 
ADD COLUMN IF NOT EXISTS address VARCHAR(255),
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS country_id BIGINT REFERENCES countries(country_id),
ADD COLUMN IF NOT EXISTS latitude DECIMAL(10, 8),
ADD COLUMN IF NOT EXISTS longitude DECIMAL(11, 8),
ADD COLUMN IF NOT EXISTS phone VARCHAR(50),
ADD COLUMN IF NOT EXISTS email VARCHAR(255),
ADD COLUMN IF NOT EXISTS website VARCHAR(500),
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Add columns to adventures table
ALTER TABLE public.adventures 
ADD COLUMN IF NOT EXISTS address VARCHAR(255),
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS country_id BIGINT REFERENCES countries(country_id),
ADD COLUMN IF NOT EXISTS latitude DECIMAL(10, 8),
ADD COLUMN IF NOT EXISTS longitude DECIMAL(11, 8),
ADD COLUMN IF NOT EXISTS phone VARCHAR(50),
ADD COLUMN IF NOT EXISTS email VARCHAR(255),
ADD COLUMN IF NOT EXISTS website VARCHAR(500),
ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_markets_country_id ON public.markets(country_id);
CREATE INDEX IF NOT EXISTS idx_markets_city ON public.markets(city);
CREATE INDEX IF NOT EXISTS idx_markets_location ON public.markets(latitude, longitude);

CREATE INDEX IF NOT EXISTS idx_adventures_country_id ON public.adventures(country_id);
CREATE INDEX IF NOT EXISTS idx_adventures_city ON public.adventures(city);
CREATE INDEX IF NOT EXISTS idx_adventures_location ON public.adventures(latitude, longitude);

-- Create triggers to automatically update updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Drop existing triggers if they exist
DROP TRIGGER IF EXISTS update_markets_updated_at ON public.markets;
DROP TRIGGER IF EXISTS update_adventures_updated_at ON public.adventures;

-- Create triggers for both tables
CREATE TRIGGER update_markets_updated_at 
    BEFORE UPDATE ON public.markets 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_adventures_updated_at 
    BEFORE UPDATE ON public.adventures 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add comments for documentation
COMMENT ON COLUMN public.markets.address IS 'Physical address of the market';
COMMENT ON COLUMN public.markets.city IS 'City where the market is located';
COMMENT ON COLUMN public.markets.country_id IS 'Foreign key reference to countries table';
COMMENT ON COLUMN public.markets.latitude IS 'GPS latitude coordinate';
COMMENT ON COLUMN public.markets.longitude IS 'GPS longitude coordinate';
COMMENT ON COLUMN public.markets.phone IS 'Contact phone number';
COMMENT ON COLUMN public.markets.email IS 'Contact email address';
COMMENT ON COLUMN public.markets.website IS 'Website URL';
COMMENT ON COLUMN public.markets.updated_at IS 'Timestamp of last update';

COMMENT ON COLUMN public.adventures.address IS 'Physical address where adventure starts/takes place';
COMMENT ON COLUMN public.adventures.city IS 'City where the adventure is located';
COMMENT ON COLUMN public.adventures.country_id IS 'Foreign key reference to countries table';
COMMENT ON COLUMN public.adventures.latitude IS 'GPS latitude coordinate';
COMMENT ON COLUMN public.adventures.longitude IS 'GPS longitude coordinate';
COMMENT ON COLUMN public.adventures.phone IS 'Contact phone number for adventure bookings';
COMMENT ON COLUMN public.adventures.email IS 'Contact email address for adventure bookings';
COMMENT ON COLUMN public.adventures.website IS 'Website URL for more information about the adventure';
COMMENT ON COLUMN public.adventures.updated_at IS 'Timestamp of last update';

-- Create market_photos table
CREATE TABLE IF NOT EXISTS public.market_photos (
    photo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    market_id UUID NOT NULL REFERENCES public.markets(market_id) ON DELETE CASCADE,
    photo_path VARCHAR(1000) NOT NULL,
    photo_url VARCHAR(2000),
    photo_caption TEXT,
    is_primary BOOLEAN DEFAULT false,
    photo_type VARCHAR(50) DEFAULT 'general', -- 'general', 'interior', 'exterior', 'products', 'logo'
    file_size BIGINT,
    width INTEGER,
    height INTEGER,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create adventure_photos table
CREATE TABLE IF NOT EXISTS public.adventure_photos (
    photo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    adventure_id UUID NOT NULL REFERENCES public.adventures(adventure_id) ON DELETE CASCADE,
    photo_path VARCHAR(1000) NOT NULL,
    photo_url VARCHAR(2000),
    photo_caption TEXT,
    is_primary BOOLEAN DEFAULT false,
    photo_type VARCHAR(50) DEFAULT 'general', -- 'general', 'equipment', 'location', 'action', 'logo'
    file_size BIGINT,
    width INTEGER,
    height INTEGER,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for photo tables
CREATE INDEX IF NOT EXISTS idx_market_photos_market_id ON public.market_photos(market_id);
CREATE INDEX IF NOT EXISTS idx_market_photos_is_primary ON public.market_photos(is_primary) WHERE is_primary = true;
CREATE INDEX IF NOT EXISTS idx_market_photos_type ON public.market_photos(photo_type);

CREATE INDEX IF NOT EXISTS idx_adventure_photos_adventure_id ON public.adventure_photos(adventure_id);
CREATE INDEX IF NOT EXISTS idx_adventure_photos_is_primary ON public.adventure_photos(is_primary) WHERE is_primary = true;
CREATE INDEX IF NOT EXISTS idx_adventure_photos_type ON public.adventure_photos(photo_type);

-- Add comments for photo tables
COMMENT ON TABLE public.market_photos IS 'Photos associated with markets';
COMMENT ON COLUMN public.market_photos.photo_id IS 'Unique identifier for the photo';
COMMENT ON COLUMN public.market_photos.market_id IS 'Foreign key reference to markets table';
COMMENT ON COLUMN public.market_photos.photo_path IS 'Local file path to the photo';
COMMENT ON COLUMN public.market_photos.photo_url IS 'External URL to the photo';
COMMENT ON COLUMN public.market_photos.photo_caption IS 'Description or caption for the photo';
COMMENT ON COLUMN public.market_photos.is_primary IS 'Whether this is the primary/main photo for the market';
COMMENT ON COLUMN public.market_photos.photo_type IS 'Type/category of photo (general, interior, exterior, products, logo)';
COMMENT ON COLUMN public.market_photos.file_size IS 'File size in bytes';
COMMENT ON COLUMN public.market_photos.width IS 'Image width in pixels';
COMMENT ON COLUMN public.market_photos.height IS 'Image height in pixels';
COMMENT ON COLUMN public.market_photos.uploaded_at IS 'Timestamp when photo was uploaded';
COMMENT ON COLUMN public.market_photos.created_at IS 'Timestamp when record was created';

COMMENT ON TABLE public.adventure_photos IS 'Photos associated with adventures';
COMMENT ON COLUMN public.adventure_photos.photo_id IS 'Unique identifier for the photo';
COMMENT ON COLUMN public.adventure_photos.adventure_id IS 'Foreign key reference to adventures table';
COMMENT ON COLUMN public.adventure_photos.photo_path IS 'Local file path to the photo';
COMMENT ON COLUMN public.adventure_photos.photo_url IS 'External URL to the photo';
COMMENT ON COLUMN public.adventure_photos.photo_caption IS 'Description or caption for the photo';
COMMENT ON COLUMN public.adventure_photos.is_primary IS 'Whether this is the primary/main photo for the adventure';
COMMENT ON COLUMN public.adventure_photos.photo_type IS 'Type/category of photo (general, equipment, location, action, logo)';
COMMENT ON COLUMN public.adventure_photos.file_size IS 'File size in bytes';
COMMENT ON COLUMN public.adventure_photos.width IS 'Image width in pixels';
COMMENT ON COLUMN public.adventure_photos.height IS 'Image height in pixels';
COMMENT ON COLUMN public.adventure_photos.uploaded_at IS 'Timestamp when photo was uploaded';
COMMENT ON COLUMN public.adventure_photos.created_at IS 'Timestamp when record was created';