CREATE TABLE advanced_goals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NULL,
    name VARCHAR(150) NOT NULL,
    goal_type VARCHAR(60) NOT NULL,
    periodicity ENUM('daily','weekly','monthly','quarterly','yearly','custom') NOT NULL DEFAULT 'monthly',
    target_mode ENUM('value','volume','percentage','duration','composite') NOT NULL DEFAULT 'value',
    default_target_value DECIMAL(15, 2) NOT NULL DEFAULT 0,
    rolling_window_days INT UNSIGNED NOT NULL DEFAULT 30,
    start_date DATE NOT NULL,
    end_date DATE NULL,
    status ENUM('draft','active','archived','completed') NOT NULL DEFAULT 'draft',
    description TEXT NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_advanced_goals_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_advanced_goals_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX idx_advanced_goals_code ON advanced_goals (code);
CREATE INDEX idx_advanced_goals_period ON advanced_goals (periodicity, start_date, end_date);
CREATE INDEX idx_advanced_goals_status ON advanced_goals (status);

CREATE TABLE goal_indicators (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(100) NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    formula TEXT NULL,
    weight DECIMAL(8, 2) NOT NULL DEFAULT 1,
    unit_type ENUM('currency','count','percentage','duration','composite') NOT NULL DEFAULT 'count',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY idx_goal_indicators_slug (slug)
);

CREATE TABLE goal_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id INT UNSIGNED NOT NULL,
    owner_type ENUM('organization','broker','employee','team') NOT NULL DEFAULT 'organization',
    owner_id INT UNSIGNED NULL,
    effective_start DATE NOT NULL,
    effective_end DATE NULL,
    target_value DECIMAL(15, 2) NOT NULL DEFAULT 0,
    status ENUM('draft','active','paused','completed','archived') NOT NULL DEFAULT 'draft',
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_goal_assignments_goal FOREIGN KEY (goal_id) REFERENCES advanced_goals (id) ON DELETE CASCADE,
    CONSTRAINT fk_goal_assignments_owner FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_goal_assignments_creator FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_goal_assignments_updater FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

CREATE INDEX idx_goal_assignments_owner ON goal_assignments (owner_type, owner_id);
CREATE INDEX idx_goal_assignments_period ON goal_assignments (effective_start, effective_end);
CREATE INDEX idx_goal_assignments_status ON goal_assignments (status);

CREATE TABLE goal_assignment_indicators (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    assignment_id INT UNSIGNED NOT NULL,
    indicator_id INT UNSIGNED NOT NULL,
    target_value DECIMAL(15, 4) NULL,
    min_threshold DECIMAL(15, 4) NULL,
    max_threshold DECIMAL(15, 4) NULL,
    weight DECIMAL(8, 2) NOT NULL DEFAULT 1,
    aggregation ENUM('sum','avg','count','custom') NOT NULL DEFAULT 'sum',
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_goal_assignment_indicators_assignment FOREIGN KEY (assignment_id) REFERENCES goal_assignments (id) ON DELETE CASCADE,
    CONSTRAINT fk_goal_assignment_indicators_indicator FOREIGN KEY (indicator_id) REFERENCES goal_indicators (id) ON DELETE CASCADE,
    UNIQUE KEY idx_goal_assignment_indicator_unique (assignment_id, indicator_id)
);

CREATE TABLE goal_indicator_values (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    assignment_id INT UNSIGNED NULL,
    goal_id INT UNSIGNED NULL,
    indicator_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    captured_on DATE NOT NULL,
    value DECIMAL(15, 4) NOT NULL,
    delta DECIMAL(15, 4) NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_goal_indicator_values_assignment FOREIGN KEY (assignment_id) REFERENCES goal_assignments (id) ON DELETE SET NULL,
    CONSTRAINT fk_goal_indicator_values_goal FOREIGN KEY (goal_id) REFERENCES advanced_goals (id) ON DELETE SET NULL,
    CONSTRAINT fk_goal_indicator_values_indicator FOREIGN KEY (indicator_id) REFERENCES goal_indicators (id) ON DELETE CASCADE,
    CONSTRAINT fk_goal_indicator_values_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    UNIQUE KEY idx_goal_indicator_values_unique (indicator_id, user_id, captured_on, goal_id, assignment_id)
);

CREATE INDEX idx_goal_indicator_values_captured ON goal_indicator_values (captured_on);