schema.sql

-- Trigger to automatically update status on insert or update
DROP TRIGGER IF EXISTS trigger_update_license_status ON licenses;
CREATE TRIGGER trigger_update_license_status
    BEFORE INSERT OR UPDATE ON licenses
    FOR EACH ROW
    EXECUTE FUNCTION update_license_status();

-- ============================================================
-- VIEWS for common queries
-- ============================================================

-- View: Active licenses with group information
CREATE OR REPLACE VIEW v_active_licenses AS
SELECT 
    l.id,
    l.key,
    l.group_id,
    lg.name AS group_name,
    l.created_at,
    l.expires_at,
    l.assigned_to,
    EXTRACT(DAY FROM (l.expires_at - NOW())) AS days_remaining
FROM licenses l
JOIN license_groups lg ON l.group_id = lg.id
WHERE l.status = 'active' AND l.expires_at > NOW();

COMMENT ON VIEW v_active_licenses IS 'Shows all active (non-expired) licenses with group information';

-- View: Expired licenses
CREATE OR REPLACE VIEW v_expired_licenses AS
SELECT 
    l.id,
    l.key,
    l.group_id,
    lg.name AS group_name,
    l.created_at,
    l.expires_at,
    l.assigned_to,
    EXTRACT(DAY FROM (NOW() - l.expires_at)) AS days_expired
FROM licenses l
JOIN license_groups lg ON l.group_id = lg.id
WHERE l.expires_at <= NOW();

COMMENT ON VIEW v_expired_licenses IS 'Shows all expired licenses';

-- View: License statistics by group
CREATE OR REPLACE VIEW v_license_statistics AS
SELECT 
    lg.id AS group_id,
    lg.name AS group_name,
    COUNT(l.id) AS total_licenses,
    COUNT(l.id) FILTER (WHERE l.status = 'active' AND l.expires_at > NOW()) AS active_licenses,
    COUNT(l.id) FILTER (WHERE l.expires_at <= NOW()) AS expired_licenses,
    COUNT(l.id) FILTER (WHERE l.assigned_to IS NOT NULL) AS assigned_licenses,
    MIN(l.expires_at) AS earliest_expiration,
    MAX(l.expires_at) AS latest_expiration
FROM license_groups lg
LEFT JOIN licenses l ON lg.id = l.group_id
GROUP BY lg.id, lg.name;

COMMENT ON VIEW v_license_statistics IS 'Shows statistics for each license group';

-- ============================================================
-- FUNCTIONS for license management
-- ============================================================

-- Function: Get license status with detailed information
CREATE OR REPLACE FUNCTION get_license_info(license_key VARCHAR)
RETURNS TABLE (
    key VARCHAR,
    group_name VARCHAR,
    status VARCHAR,
    is_expired BOOLEAN,
    created_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    time_remaining INTERVAL,
    assigned_to VARCHAR
) AS $
BEGIN
    RETURN QUERY
    SELECT 
        l.key,
        lg.name AS group_name,
        l.status,
        (l.expires_at <= NOW()) AS is_expired,
        l.created_at,
        l.expires_at,
        (l.expires_at - NOW()) AS time_remaining,
        l.assigned_to
    FROM licenses l
    JOIN license_groups lg ON l.group_id = lg.id
    WHERE l.key = license_key;
END;
$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_license_info IS 'Returns detailed information about a specific license';

-- Function: Count licenses by status
CREATE OR REPLACE FUNCTION count_licenses_by_status()
RETURNS TABLE (
    status VARCHAR,
    count BIGINT
) AS $
BEGIN
    RETURN QUERY
    SELECT 
        l.status,
        COUNT(*)::BIGINT
    FROM licenses l
    GROUP BY l.status;
END;
$ LANGUAGE plpgsql;

COMMENT ON FUNCTION count_licenses_by_status IS 'Returns count of licenses grouped by status';

-- Function: Get expiring licenses (within specified days)
CREATE OR REPLACE FUNCTION get_expiring_licenses(days_ahead INTEGER DEFAULT 7)
RETURNS TABLE (
    key VARCHAR,
    group_name VARCHAR,
    expires_at TIMESTAMP WITH TIME ZONE,
    days_until_expiration NUMERIC,
    assigned_to VARCHAR
) AS $
BEGIN
    RETURN QUERY
    SELECT 
        l.key,
        lg.name AS group_name,
        l.expires_at,
        EXTRACT(DAY FROM (l.expires_at - NOW()))::NUMERIC AS days_until_expiration,
        l.assigned_to
    FROM licenses l
    JOIN license_groups lg ON l.group_id = lg.id
    WHERE l.status = 'active' 
        AND l.expires_at > NOW() 
        AND l.expires_at <= NOW() + (days_ahead || ' days')::INTERVAL
    ORDER BY l.expires_at ASC;
END;
$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_expiring_licenses IS 'Returns licenses expiring within specified number of days';

-- ============================================================
-- ROW LEVEL SECURITY (Optional - uncomment if needed)
-- ============================================================

-- Enable RLS on tables
-- ALTER TABLE license_groups ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE licenses ENABLE ROW LEVEL SECURITY;

-- Create policies (example - adjust based on your needs)
-- CREATE POLICY "Allow public read access to license_groups" ON license_groups
--     FOR SELECT USING (true);

-- CREATE POLICY "Allow public read access to licenses" ON licenses
--     FOR SELECT USING (true);

-- CREATE POLICY "Allow authenticated users to insert licenses" ON licenses
--     FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- CREATE POLICY "Allow authenticated users to update licenses" ON licenses
--     FOR UPDATE USING (auth.role() = 'authenticated');

-- CREATE POLICY "Allow authenticated users to delete licenses" ON licenses
--     FOR DELETE USING (auth.role() = 'authenticated');

-- ============================================================
-- INITIAL DATA (Optional - sample license groups)
-- ============================================================

-- Uncomment to insert sample license groups
-- INSERT INTO license_groups (name) VALUES 
--     ('PREMIUM'),
--     ('BASIC'),
--     ('ENTERPRISE')
-- ON CONFLICT (name) DO NOTHING;

-- ============================================================
-- UTILITY FUNCTIONS
-- ============================================================

-- Function: Clean up expired licenses older than X days
CREATE OR REPLACE FUNCTION cleanup_old_expired_licenses(days_old INTEGER DEFAULT 90)
RETURNS INTEGER AS $
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM licenses
    WHERE status = 'expired' 
        AND expires_at < NOW() - (days_old || ' days')::INTERVAL;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_old_expired_licenses IS 'Deletes expired licenses older than specified days (default 90)';

-- Function: Bulk extend licenses by group
CREATE OR REPLACE FUNCTION bulk_extend_licenses(
    target_group_name VARCHAR,
    additional_days INTEGER
)
RETURNS INTEGER AS $
DECLARE
    updated_count INTEGER;
    target_group_id UUID;
BEGIN
    -- Get group ID
    SELECT id INTO target_group_id
    FROM license_groups
    WHERE name = target_group_name;
    
    IF target_group_id IS NULL THEN
        RAISE EXCEPTION 'License group not found: %', target_group_name;
    END IF;
    
    -- Update all licenses in the group
    UPDATE licenses
    SET expires_at = expires_at + (additional_days || ' days')::INTERVAL,
        status = 'active'
    WHERE group_id = target_group_id;
    
    GET DIAGNOSTICS updated_count = ROW_COUNT;
    RETURN updated_count;
END;
$ LANGUAGE plpgsql;

COMMENT ON FUNCTION bulk_extend_licenses IS 'Extends all licenses in a group by specified days';

-- ============================================================
-- SCHEDULED JOBS (Using pg_cron extension if available)
-- ============================================================

-- Note: pg_cron might not be available on all Supabase plans
-- Uncomment and adjust if you have pg_cron enabled

-- Enable pg_cron extension
-- CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule automatic status updates (runs every hour)
-- SELECT cron.schedule(
--     'update-expired-licenses',
--     '0 * * * *', -- Every hour
--     $
--     UPDATE licenses 
--     SET status = 'expired' 
--     WHERE expires_at <= NOW() AND status = 'active'
--     $
-- );

-- Schedule cleanup of very old expired licenses (runs weekly)
-- SELECT cron.schedule(
--     'cleanup-old-licenses',
--     '0 0 * * 0', -- Every Sunday at midnight
--     $
--     SELECT cleanup_old_expired_licenses(90)
--     $
-- );

-- ============================================================
-- PERFORMANCE MONITORING
-- ============================================================

-- View: Table sizes
CREATE OR REPLACE VIEW v_table_sizes AS
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

COMMENT ON VIEW v_table_sizes IS 'Shows size of all tables and their indexes';

-- ============================================================
-- AUDIT LOG (Optional - for tracking changes)
-- ============================================================

-- Uncomment if you want to track all changes to licenses

-- CREATE TABLE IF NOT EXISTS license_audit_log (
--     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
--     license_id UUID REFERENCES licenses(id) ON DELETE SET NULL,
--     license_key VARCHAR(255),
--     action VARCHAR(50) NOT NULL, -- 'created', 'updated', 'deleted', 'extended', 'assigned'
--     old_values JSONB,
--     new_values JSONB,
--     changed_by VARCHAR(255),
--     changed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
-- );

-- CREATE INDEX IF NOT EXISTS idx_audit_log_license_id ON license_audit_log(license_id);
-- CREATE INDEX IF NOT EXISTS idx_audit_log_action ON license_audit_log(action);
-- CREATE INDEX IF NOT EXISTS idx_audit_log_changed_at ON license_audit_log(changed_at);

-- -- Audit trigger function
-- CREATE OR REPLACE FUNCTION audit_license_changes()
-- RETURNS TRIGGER AS $
-- BEGIN
--     IF TG_OP = 'INSERT' THEN
--         INSERT INTO license_audit_log (license_id, license_key, action, new_values)
--         VALUES (NEW.id, NEW.key, 'created', row_to_json(NEW)::JSONB);
--     ELSIF TG_OP = 'UPDATE' THEN
--         INSERT INTO license_audit_log (license_id, license_key, action, old_values, new_values)
--         VALUES (NEW.id, NEW.key, 'updated', row_to_json(OLD)::JSONB, row_to_json(NEW)::JSONB);
--     ELSIF TG_OP = 'DELETE' THEN
--         INSERT INTO license_audit_log (license_id, license_key, action, old_values)
--         VALUES (OLD.id, OLD.key, 'deleted', row_to_json(OLD)::JSONB);
--     END IF;
--     RETURN NULL;
-- END;
-- $ LANGUAGE plpgsql;

-- -- Attach audit trigger
-- DROP TRIGGER IF EXISTS trigger_audit_license_changes ON licenses;
-- CREATE TRIGGER trigger_audit_license_changes
--     AFTER INSERT OR UPDATE OR DELETE ON licenses
--     FOR EACH ROW EXECUTE FUNCTION audit_license_changes();

-- ============================================================
-- VERIFICATION & TESTING
-- ============================================================

-- Query to verify all tables were created
SELECT 
    table_name,
    table_type
FROM information_schema.tables
WHERE table_schema = 'public'
    AND table_name IN ('license_groups', 'licenses')
ORDER BY table_name;

-- Query to verify all indexes were created
SELECT 
    indexname,
    tablename,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
    AND tablename IN ('license_groups', 'licenses')
ORDER BY tablename, indexname;

-- Query to verify all views were created
SELECT 
    table_name AS view_name
FROM information_schema.views
WHERE table_schema = 'public'
    AND table_name LIKE 'v_%'
ORDER BY table_name;

-- Query to verify all functions were created
SELECT 
    routine_name AS function_name,
    routine_type
FROM information_schema.routines
WHERE routine_schema = 'public'
    AND routine_type = 'FUNCTION'
ORDER BY routine_name;

-- ============================================================
-- SAMPLE QUERIES for testing
-- ============================================================

-- Test: Create a sample license group and licenses
-- INSERT INTO license_groups (name) VALUES ('TEST') RETURNING *;

-- Test: Create sample licenses
-- INSERT INTO licenses (key, group_id, expires_at, status)
-- SELECT 
--     'OP-TEST-TI-SAMPLE' || i || '-C',
--     (SELECT id FROM license_groups WHERE name = 'TEST'),
--     NOW() + interval '30 days',
--     'active'
-- FROM generate_series(1, 5) AS i;

-- Test: Query active licenses
-- SELECT * FROM v_active_licenses WHERE group_name = 'TEST';

-- Test: Get license info
-- SELECT * FROM get_license_info('OP-TEST-TI-SAMPLE1-C');

-- Test: Get expiring licenses
-- SELECT * FROM get_expiring_licenses(30);

-- ============================================================
-- SCHEMA COMPLETE
-- ============================================================

-- Display success message
DO $ 
BEGIN 
    RAISE NOTICE '============================================================';
    RAISE NOTICE 'License Management System schema created successfully!';
    RAISE NOTICE '============================================================';
    RAISE NOTICE 'Tables created: license_groups, licenses';
    RAISE NOTICE 'Indexes created: Multiple indexes for performance';
    RAISE NOTICE 'Views created: v_active_licenses, v_expired_licenses, v_license_statistics';
    RAISE NOTICE 'Functions created: get_license_info, count_licenses_by_status, and more';
    RAISE NOTICE '============================================================';
    RAISE NOTICE 'Next steps:';
    RAISE NOTICE '1. Configure your .env file with Supabase credentials';
    RAISE NOTICE '2. Run: python bot.py';
    RAISE NOTICE '3. Run: python api.py (in separate terminal)';
    RAISE NOTICE '============================================================';
END $; ============================================================
-- LICENSE MANAGEMENT SYSTEM - SUPABASE DATABASE SCHEMA
-- ============================================================
-- Run this script in your Supabase SQL Editor to create
-- all necessary tables and indexes for the licensing system
-- ============================================================

-- Enable UUID extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ============================================================
-- TABLE: license_groups
-- Stores license groups (e.g., Premium, Basic, Enterprise)
-- ============================================================

CREATE TABLE IF NOT EXISTS license_groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Constraints
    CONSTRAINT name_not_empty CHECK (char_length(name) > 0)
);

-- Add comment for documentation
COMMENT ON TABLE license_groups IS 'Stores license groups for organizing licenses by product or tier';
COMMENT ON COLUMN license_groups.id IS 'Unique identifier for the license group';
COMMENT ON COLUMN license_groups.name IS 'Unique name of the license group (e.g., PREMIUM, BASIC)';
COMMENT ON COLUMN license_groups.created_at IS 'Timestamp when the group was created';

-- ============================================================
-- TABLE: licenses
-- Stores individual license keys with expiration and status
-- ============================================================

CREATE TABLE IF NOT EXISTS licenses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    key VARCHAR(255) UNIQUE NOT NULL,
    group_id UUID NOT NULL REFERENCES license_groups(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(50) DEFAULT 'active',
    assigned_to VARCHAR(255),
    
    -- Constraints
    CONSTRAINT key_not_empty CHECK (char_length(key) > 0),
    CONSTRAINT status_valid CHECK (status IN ('active', 'expired', 'revoked', 'suspended')),
    CONSTRAINT expires_after_creation CHECK (expires_at > created_at)
);

-- Add comment for documentation
COMMENT ON TABLE licenses IS 'Stores individual license keys with metadata';
COMMENT ON COLUMN licenses.id IS 'Unique identifier for the license';
COMMENT ON COLUMN licenses.key IS 'Unique license key (e.g., OP-PREMIUM-TI-ABC123-C)';
COMMENT ON COLUMN licenses.group_id IS 'Reference to the license group this license belongs to';
COMMENT ON COLUMN licenses.created_at IS 'Timestamp when the license was created';
COMMENT ON COLUMN licenses.expires_at IS 'Timestamp when the license expires';
COMMENT ON COLUMN licenses.status IS 'Current status of the license (active, expired, revoked, suspended)';
COMMENT ON COLUMN licenses.assigned_to IS 'Optional: Username or tool identifier assigned to this license';

-- ============================================================
-- INDEXES for faster queries
-- ============================================================

-- Index on license keys for quick lookups (most common query)
CREATE INDEX IF NOT EXISTS idx_licenses_key ON licenses(key);

-- Index on group_id for filtering licenses by group
CREATE INDEX IF NOT EXISTS idx_licenses_group_id ON licenses(group_id);

-- Index on status for filtering active/expired licenses
CREATE INDEX IF NOT EXISTS idx_licenses_status ON licenses(status);

-- Index on expires_at for finding expiring licenses
CREATE INDEX IF NOT EXISTS idx_licenses_expires_at ON licenses(expires_at);

-- Index on assigned_to for finding user's licenses
CREATE INDEX IF NOT EXISTS idx_licenses_assigned_to ON licenses(assigned_to) WHERE assigned_to IS NOT NULL;

-- Index on license_groups name for quick group lookups
CREATE INDEX IF NOT EXISTS idx_license_groups_name ON license_groups(name);

-- Composite index for common query patterns
CREATE INDEX IF NOT EXISTS idx_licenses_group_status ON licenses(group_id, status);
CREATE INDEX IF NOT EXISTS idx_licenses_status_expires ON licenses(status, expires_at);

-- ============================================================
-- FUNCTIONS for automatic status updates
-- ============================================================

-- Function to automatically update license status based on expiration
CREATE OR REPLACE FUNCTION update_license_status()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.expires_at <= NOW() THEN
        NEW.status := 'expired';
    ELSIF NEW.status = 'expired' AND NEW.expires_at > NOW() THEN
        NEW.status := 'active';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--