-- TTS Service Supabase Schema

-- Users table
CREATE TABLE IF NOT EXISTS tts_users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'premium')),
    daily_limit INTEGER DEFAULT 3,  -- -1 for unlimited
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Usage logs
CREATE TABLE IF NOT EXISTS tts_usage_logs (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    text_length INTEGER NOT NULL,
    language VARCHAR(10) DEFAULT 'en',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    FOREIGN KEY (username) REFERENCES tts_users(username) ON DELETE CASCADE
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_tts_users_username ON tts_users(username);
CREATE INDEX IF NOT EXISTS idx_tts_usage_logs_username ON tts_usage_logs(username);
CREATE INDEX IF NOT EXISTS idx_tts_usage_logs_created_at ON tts_usage_logs(created_at);

-- Enable Row Level Security
ALTER TABLE tts_users ENABLE ROW LEVEL SECURITY;
ALTER TABLE tts_usage_logs ENABLE ROW LEVEL SECURITY;

-- Allow service role to do everything
CREATE POLICY "Service role can do everything on tts_users"
    ON tts_users
    FOR ALL
    TO service_role
    USING (true);

CREATE POLICY "Service role can do everything on tts_usage_logs"
    ON tts_usage_logs
    FOR ALL
    TO service_role
    USING (true);

-- Admin user will be created via Python code with bcrypt hash