🚀 Supabase Database Setup

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(20) DEFAULT 'web' CHECK (type IN ('web', 'mobile', 'analysis')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    user_id BIGINT DEFAULT 1
);

-- Create updated_at trigger for projects
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_projects_updated_at 
    BEFORE UPDATE ON projects 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Data Sources table
CREATE TABLE IF NOT EXISTS data_sources (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    size BIGINT NOT NULL,
    uploaded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Styles table
CREATE TABLE IF NOT EXISTS styles (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT REFERENCES projects(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    style_json TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create updated_at trigger for styles
CREATE TRIGGER update_styles_updated_at 
    BEFORE UPDATE ON styles 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

-- Users table (optional - for user management)
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255),
    full_name VARCHAR(255),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

-- Tiles table (for map tiles or similar data)
CREATE TABLE IF NOT EXISTS tiles (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT REFERENCES projects(id) ON DELETE CASCADE,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    z INTEGER NOT NULL,
    data BYTEA,
    content_type VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(project_id, x, y, z)
);

-- Queue Items table (for processing queue)
CREATE TABLE IF NOT EXISTS queue_items (
    id BIGSERIAL PRIMARY KEY,
    project_id BIGINT REFERENCES projects(id) ON DELETE CASCADE,
    filename VARCHAR(255) NOT NULL,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    error_message TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    processed_at TIMESTAMPTZ
);

-- Create updated_at trigger for queue_items
CREATE TRIGGER update_queue_items_updated_at 
    BEFORE UPDATE ON queue_items 
    FOR EACH ROW 
    EXECUTE FUNCTION update_updated_at_column();

CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id);
CREATE INDEX IF NOT EXISTS idx_projects_type ON projects(type);
CREATE INDEX IF NOT EXISTS idx_data_sources_project_id ON data_sources(project_id);
CREATE INDEX IF NOT EXISTS idx_styles_project_id ON styles(project_id);
CREATE INDEX IF NOT EXISTS idx_tiles_project_id ON tiles(project_id);
CREATE INDEX IF NOT EXISTS idx_tiles_coords ON tiles(x, y, z);
CREATE INDEX IF NOT EXISTS idx_queue_items_project_id ON queue_items(project_id);
CREATE INDEX IF NOT EXISTS idx_queue_items_status ON queue_items(status);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Enable Row Level Security
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE data_sources ENABLE ROW LEVEL SECURITY;
ALTER TABLE styles ENABLE ROW LEVEL SECURITY;
ALTER TABLE tiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE queue_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Basic RLS Policies (customize as needed)
-- Allow authenticated users to read all projects
CREATE POLICY "Allow authenticated read access" ON projects
    FOR SELECT USING (auth.role() = 'authenticated');

-- Allow users to manage their own projects
CREATE POLICY "Users can manage own projects" ON projects
    FOR ALL USING (auth.uid()::text = user_id::text);

-- Allow access to related data based on project ownership
CREATE POLICY "Access data_sources via project" ON data_sources
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM projects 
            WHERE projects.id = data_sources.project_id 
            AND projects.user_id::text = auth.uid()::text
        )
    );

CREATE POLICY "Access styles via project" ON styles
    FOR ALL USING (
        EXISTS (
            SELECT 1 FROM projects 
            WHERE projects.id = styles.project_id 
            AND projects.user_id::text = auth.uid()::text
        )
    );

-- Insert sample user
INSERT INTO users (username, email, full_name) VALUES 
('admin', 'admin@example.com', 'Administrator');

-- Insert sample projects
INSERT INTO projects (name, description, type, user_id) VALUES 
('Sample Web Project', 'A sample web mapping project', 'web', 1),
('Mobile App Project', 'A sample mobile mapping project', 'mobile', 1),
('Data Analysis Project', 'A sample data analysis project', 'analysis', 1);

-- Insert sample data source
INSERT INTO data_sources (project_id, name, filename, type, size) VALUES 
(1, 'Sample Dataset', 'sample_data.geojson', 'geojson', 1024);