template-saas-database-v2/ddl/schemas/goals/02-tables.sql

-- ============================================
-- SAAS-022: Goals Tables
-- ============================================

-- ─────────────────────────────────────────────
-- Definiciones de metas
-- ─────────────────────────────────────────────
CREATE TABLE goals.definitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Info basica
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(100),

    -- Tipo de meta
    type goals.goal_type NOT NULL DEFAULT 'target',
    metric goals.metric_type NOT NULL DEFAULT 'number',

    -- Objetivo
    target_value DECIMAL(15,2) NOT NULL,
    unit VARCHAR(50), -- 'USD', 'units', '%', etc.

    -- Periodo
    period goals.period_type NOT NULL DEFAULT 'monthly',
    starts_at DATE NOT NULL,
    ends_at DATE NOT NULL,

    -- Fuente de datos (para tracking automatico)
    source goals.data_source NOT NULL DEFAULT 'manual',
    source_config JSONB DEFAULT '{}',
    -- Ejemplo:
    -- {
    --   "module": "sales",
    --   "entity": "opportunities",
    --   "filter": { "status": "won" },
    --   "aggregation": "sum",
    --   "field": "amount"
    -- }

    -- Hitos para notificaciones
    milestones JSONB DEFAULT '[]',
    -- Ejemplo: [{ "percentage": 25, "notify": true }, { "percentage": 50, "notify": true }]

    -- Estado
    status goals.goal_status NOT NULL DEFAULT 'draft',

    -- Metadata
    tags JSONB DEFAULT '[]',

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID REFERENCES users.users(id),

    -- Constraints
    CONSTRAINT valid_date_range CHECK (ends_at >= starts_at),
    CONSTRAINT positive_target CHECK (target_value > 0)
);

-- ─────────────────────────────────────────────
-- Asignaciones de metas
-- ─────────────────────────────────────────────
CREATE TABLE goals.assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    definition_id UUID NOT NULL REFERENCES goals.definitions(id) ON DELETE CASCADE,

    -- Asignado a
    assignee_type goals.assignee_type NOT NULL DEFAULT 'user',
    user_id UUID REFERENCES users.users(id) ON DELETE CASCADE,
    team_id UUID, -- Para uso futuro con modulo de equipos

    -- Objetivo personalizado (override del definition)
    custom_target DECIMAL(15,2),

    -- Progreso
    current_value DECIMAL(15,2) NOT NULL DEFAULT 0,
    progress_percentage DECIMAL(5,2) NOT NULL DEFAULT 0,
    last_updated_at TIMESTAMPTZ,

    -- Estado
    status goals.assignment_status NOT NULL DEFAULT 'active',
    achieved_at TIMESTAMPTZ,

    -- Metadata
    notes TEXT,

    -- Timestamps
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT valid_progress CHECK (progress_percentage >= 0 AND progress_percentage <= 100),
    CONSTRAINT valid_assignee CHECK (
        (assignee_type = 'user' AND user_id IS NOT NULL) OR
        (assignee_type = 'team' AND team_id IS NOT NULL) OR
        (assignee_type = 'tenant')
    )
);

-- ─────────────────────────────────────────────
-- Historial de progreso
-- ─────────────────────────────────────────────
CREATE TABLE goals.progress_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    assignment_id UUID NOT NULL REFERENCES goals.assignments(id) ON DELETE CASCADE,

    -- Valores
    previous_value DECIMAL(15,2),
    new_value DECIMAL(15,2) NOT NULL,
    change_amount DECIMAL(15,2),

    -- Fuente del cambio
    source goals.progress_source NOT NULL DEFAULT 'manual',
    source_reference VARCHAR(200), -- ID de la transaccion origen

    notes TEXT,

    -- Timestamps
    logged_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    logged_by UUID REFERENCES users.users(id)
);

-- ─────────────────────────────────────────────
-- Notificaciones de hitos alcanzados
-- ─────────────────────────────────────────────
CREATE TABLE goals.milestone_notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    assignment_id UUID NOT NULL REFERENCES goals.assignments(id) ON DELETE CASCADE,

    milestone_percentage INTEGER NOT NULL,
    achieved_value DECIMAL(15,2) NOT NULL,

    notified_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Evitar duplicados
    CONSTRAINT unique_milestone_notification UNIQUE (assignment_id, milestone_percentage)
);

-- ─────────────────────────────────────────────
-- Triggers para updated_at
-- ─────────────────────────────────────────────
CREATE TRIGGER update_definitions_updated_at
    BEFORE UPDATE ON goals.definitions
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_assignments_updated_at
    BEFORE UPDATE ON goals.assignments
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();