CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS kanadi_users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    active_device_id UUID,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS kanadi_devices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES kanadi_users(id) ON DELETE CASCADE,
    session_id VARCHAR(255) NOT NULL,
    ip VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS kanadi_contexts (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    session_id VARCHAR(255) NOT NULL UNIQUE,
    user_id UUID NOT NULL REFERENCES kanadi_users(id) ON DELETE CASCADE,
    bundle_id VARCHAR(255),
    ip VARCHAR(45) NOT NULL,
    user_agent TEXT NOT NULL,
    referrer TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS kanadi_bundle_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bundle_id VARCHAR(255) NOT NULL,
    total_score FLOAT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS kanadi_validator_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    bundle_session_id UUID NOT NULL REFERENCES kanadi_bundle_sessions(id) ON DELETE CASCADE,
    validator_id VARCHAR(255) NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS kanadi_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    device_id UUID NOT NULL REFERENCES kanadi_devices(id) ON DELETE CASCADE,
    session_id VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_active_device ON kanadi_users(active_device_id);
CREATE INDEX idx_devices_user_id ON kanadi_devices(user_id);
CREATE INDEX idx_devices_session_id ON kanadi_devices(session_id);
CREATE INDEX idx_contexts_session_id ON kanadi_contexts(session_id);
CREATE INDEX idx_contexts_user_id ON kanadi_contexts(user_id);
CREATE INDEX idx_bundle_sessions_bundle_id ON kanadi_bundle_sessions(bundle_id);
CREATE INDEX idx_validator_sessions_bundle_session_id ON kanadi_validator_sessions(bundle_session_id);
CREATE INDEX idx_validator_sessions_validator_id ON kanadi_validator_sessions(validator_id);
CREATE INDEX idx_history_device_id ON kanadi_history(device_id);
CREATE INDEX idx_history_session_id ON kanadi_history(session_id);

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_kanadi_users_updated_at BEFORE UPDATE ON kanadi_users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kanadi_devices_updated_at BEFORE UPDATE ON kanadi_devices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kanadi_contexts_updated_at BEFORE UPDATE ON kanadi_contexts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kanadi_bundle_sessions_updated_at BEFORE UPDATE ON kanadi_bundle_sessions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_kanadi_validator_sessions_updated_at BEFORE UPDATE ON kanadi_validator_sessions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();