-- License Verification Database Schema
-- Optimized for Merkle tree construction and proof generation

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Main license storage table
CREATE TABLE licenses (
    id SERIAL PRIMARY KEY,
    license_number VARCHAR(50) UNIQUE NOT NULL,
    license_hash BYTEA NOT NULL,
    practitioner_name VARCHAR(255),
    issued_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'revoked', 'expired')),
    jurisdiction VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_license_hash ON licenses(license_hash);
CREATE INDEX idx_status ON licenses(status);
CREATE INDEX idx_expiry ON licenses(expiry_date);
CREATE INDEX idx_updated ON licenses(updated_at);
-- Merkle tree structure table
CREATE TABLE merkle_trees (
    id SERIAL PRIMARY KEY,
    tree_version INTEGER NOT NULL,
    root_hash BYTEA NOT NULL,
    tree_depth INTEGER NOT NULL DEFAULT 20,
    leaf_count INTEGER NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    finalized_at TIMESTAMP WITH TIME ZONE,
    is_active BOOLEAN DEFAULT FALSE
);
CREATE UNIQUE INDEX unique_active_tree ON merkle_trees(is_active) WHERE is_active = TRUE;

-- Index for merkle_trees
CREATE INDEX idx_active_tree ON merkle_trees(is_active, created_at DESC);


-- Merkle tree nodes (for efficient proof generation)
CREATE TABLE merkle_nodes (
    id SERIAL PRIMARY KEY,
    tree_id INTEGER REFERENCES merkle_trees(id) ON DELETE CASCADE,
    level INTEGER NOT NULL,
    position BIGINT NOT NULL,
    hash BYTEA NOT NULL,
    left_child_id INTEGER REFERENCES merkle_nodes(id),
    right_child_id INTEGER REFERENCES merkle_nodes(id),

    UNIQUE(tree_id, level, position)
);

-- Indexes for merkle_nodes
CREATE INDEX idx_tree_level ON merkle_nodes(tree_id, level);
CREATE INDEX idx_tree_position ON merkle_nodes(tree_id, level, position);

-- License to leaf mapping for quick proof generation
CREATE TABLE merkle_leaves (
    id SERIAL PRIMARY KEY,
    tree_id INTEGER REFERENCES merkle_trees(id) ON DELETE CASCADE,
    license_id INTEGER REFERENCES licenses(id),
    leaf_index BIGINT NOT NULL,
    leaf_hash TEXT NOT NULL,

    UNIQUE(tree_id, license_id),
    UNIQUE(tree_id, leaf_index)
);

-- Index for merkle_leaves
CREATE INDEX idx_license_lookup ON merkle_leaves(license_id, tree_id);


-- Proof generation cache (optional, for performance)
CREATE TABLE proof_cache (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    license_hash BYTEA NOT NULL,
    tree_id INTEGER REFERENCES merkle_trees(id) ON DELETE CASCADE,
    proof_path JSONB NOT NULL,
    proof_indices JSONB NOT NULL,
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP + INTERVAL '1 hour'
);

-- Indexes for proof_cache
CREATE INDEX idx_cache_lookup ON proof_cache(license_hash, tree_id);
CREATE INDEX idx_cache_expiry ON proof_cache(expires_at);


-- Audit log for all proof verifications
CREATE TABLE verification_audit (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proof_hash VARCHAR(64),
    merkle_root BYTEA,
    verification_result BOOLEAN NOT NULL,
    verification_time_ms INTEGER,
    client_ip INET,
    user_agent TEXT,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for verification_audit
CREATE INDEX idx_audit_time ON verification_audit(created_at DESC);
CREATE INDEX idx_audit_result ON verification_audit(verification_result);

-- Benchmark results storage
CREATE TABLE benchmark_results (
    id SERIAL PRIMARY KEY,
    test_name VARCHAR(100) NOT NULL,
    operation_type VARCHAR(50) NOT NULL,
    duration_ms INTEGER NOT NULL,
    memory_usage_mb FLOAT,
    circuit_constraints INTEGER,
    proof_size_bytes INTEGER,
    success BOOLEAN NOT NULL,
    error_details TEXT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Index for benchmark_results
CREATE INDEX idx_benchmark_type ON benchmark_results(operation_type, created_at DESC);


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

-- Apply the trigger to licenses table
CREATE TRIGGER update_licenses_updated_at BEFORE UPDATE
    ON licenses FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Function to clean expired proof cache entries
CREATE OR REPLACE FUNCTION clean_expired_proof_cache()
RETURNS void AS $$
BEGIN
    DELETE FROM proof_cache WHERE expires_at < CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

-- View for active licenses ready for Merkle tree inclusion
CREATE VIEW active_licenses_for_merkle AS
SELECT 
    id,
    license_hash,
    expiry_date
FROM licenses
WHERE status = 'active' 
    AND expiry_date > CURRENT_DATE
ORDER BY id;

-- View for Merkle tree statistics
CREATE VIEW merkle_tree_stats AS
SELECT 
    mt.id,
    mt.tree_version,
    mt.root_hash,
    mt.leaf_count,
    mt.created_at,
    mt.is_active,
    COUNT(DISTINCT mn.level) as total_levels,
    COUNT(mn.id) as total_nodes
FROM merkle_trees mt
LEFT JOIN merkle_nodes mn ON mt.id = mn.tree_id
GROUP BY mt.id;

-- Initial test data population function
CREATE OR REPLACE FUNCTION populate_test_licenses(num_licenses INTEGER DEFAULT 1000)
RETURNS void AS $$
DECLARE
    i INTEGER;
    license_num VARCHAR(50);
    hash_bytes BYTEA;
BEGIN
    FOR i IN 1..num_licenses LOOP
        license_num := 'LIC-' || LPAD(i::text, 8, '0');
        hash_bytes := sha256(license_num::bytea); -- Using SHA256 as placeholder for Poseidon
        
        INSERT INTO licenses (
            license_number,
            license_hash,
            practitioner_name,
            issued_date,
            expiry_date,
            status,
            jurisdiction
        ) VALUES (
            license_num,
            hash_bytes,
            'Test Practitioner ' || i,
            CURRENT_DATE - (random() * 365)::integer,
            CURRENT_DATE + (random() * 730 + 365)::integer, -- 1-3 years validity
            CASE WHEN random() > 0.95 THEN 'suspended' ELSE 'active' END,
            CASE (random() * 4)::integer 
                WHEN 0 THEN 'California'
                WHEN 1 THEN 'New York'
                WHEN 2 THEN 'Texas'
                ELSE 'Florida'
            END
        );
    END LOOP;
    
    RAISE NOTICE 'Populated % test licenses', num_licenses;
END;
$$ LANGUAGE plpgsql;

-- Performance indexes for large-scale operations
CREATE INDEX CONCURRENTLY idx_licenses_composite ON licenses(status, expiry_date) WHERE status = 'active';
CREATE INDEX CONCURRENTLY idx_merkle_nodes_tree_traversal ON merkle_nodes(tree_id, level DESC, position);

-- Grant permissions (adjust as needed)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO PUBLIC;
GRANT INSERT, UPDATE ON verification_audit, benchmark_results TO PUBLIC;