-- ============================================
-- TEMPLATE-SAAS: Sessions
-- Schema: auth
-- Version: 1.0.0
-- ============================================

CREATE TABLE auth.sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,

    -- Session identification
    token_hash VARCHAR(255) UNIQUE NOT NULL,  -- SHA256 of session token

    -- Status
    status auth.session_status DEFAULT 'active' NOT NULL,

    -- Device/Client info
    user_agent TEXT,
    ip_address INET,
    device_type VARCHAR(50),  -- 'desktop', 'mobile', 'tablet'
    device_name VARCHAR(200),
    browser VARCHAR(100),
    os VARCHAR(100),
    location VARCHAR(200),  -- City, Country

    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    last_activity_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    revoked_reason VARCHAR(100)
);

-- Indexes
CREATE INDEX idx_sessions_user ON auth.sessions(user_id) WHERE status = 'active';
CREATE INDEX idx_sessions_tenant ON auth.sessions(tenant_id) WHERE status = 'active';
CREATE INDEX idx_sessions_token ON auth.sessions(token_hash);
CREATE INDEX idx_sessions_expires ON auth.sessions(expires_at) WHERE status = 'active';

-- RLS
ALTER TABLE auth.sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY sessions_tenant_isolation ON auth.sessions
    USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID);

-- Function to clean expired sessions
CREATE OR REPLACE FUNCTION auth.cleanup_expired_sessions()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    WITH deleted AS (
        DELETE FROM auth.sessions
        WHERE expires_at < NOW() - INTERVAL '7 days'
        OR (status = 'revoked' AND revoked_at < NOW() - INTERVAL '30 days')
        RETURNING *
    )
    SELECT COUNT(*) INTO deleted_count FROM deleted;

    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

-- Comments
COMMENT ON TABLE auth.sessions IS 'Active user sessions with device info';
COMMENT ON COLUMN auth.sessions.token_hash IS 'SHA256 hash of session token for secure storage';
COMMENT ON COLUMN auth.sessions.revoked_reason IS 'Reason for revocation: logout, security, password_change, admin';