-- Goal Setting App schema
-- Run via public/setup.php, or manually against the database named in
-- config/database.php.
-- All tables are prefixed with g2_ to avoid collisions in shared databases.

CREATE TABLE IF NOT EXISTS g2_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    last_login_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_user_category (user_id, name),
    CONSTRAINT g2_fk_categories_user
        FOREIGN KEY (user_id) REFERENCES g2_users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_business_projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT NULL,
    stage VARCHAR(100) NULL,
    revenue_target DECIMAL(12,2) NULL,
    customer_target INT NULL,
    launch_date DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT g2_fk_business_projects_user
        FOREIGN KEY (user_id) REFERENCES g2_users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_goals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_goal_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    business_project_id BIGINT UNSIGNED NULL,

    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    motivation TEXT NULL,
    completion_description TEXT NULL,

    goal_term ENUM('long', 'medium', 'short') NULL,
    priority ENUM('low', 'normal', 'high')
        NOT NULL DEFAULT 'normal',
    status ENUM(
        'not_started',
        'in_progress',
        'paused',
        'completed',
        'cancelled'
    ) NOT NULL DEFAULT 'not_started',

    progress_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
    target_date DATE NULL,
    completed_at DATETIME NULL,
    sort_order INT NOT NULL DEFAULT 0,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT g2_fk_goals_user
        FOREIGN KEY (user_id) REFERENCES g2_users(id)
        ON DELETE CASCADE,

    CONSTRAINT g2_fk_goals_parent
        FOREIGN KEY (parent_goal_id) REFERENCES g2_goals(id)
        ON DELETE SET NULL,

    CONSTRAINT g2_fk_goals_category
        FOREIGN KEY (category_id) REFERENCES g2_categories(id)
        ON DELETE SET NULL,

    CONSTRAINT g2_fk_goals_business_project
        FOREIGN KEY (business_project_id)
        REFERENCES g2_business_projects(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_goal_actions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    status ENUM('open', 'completed', 'cancelled')
        NOT NULL DEFAULT 'open',
    due_date DATE NULL,
    sort_order INT NOT NULL DEFAULT 0,
    completed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT g2_fk_goal_actions_goal
        FOREIGN KEY (goal_id) REFERENCES g2_goals(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_goal_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id BIGINT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT g2_fk_goal_notes_goal
        FOREIGN KEY (goal_id) REFERENCES g2_goals(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS g2_goal_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    goal_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    change_type VARCHAR(100) NOT NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT g2_fk_goal_history_goal
        FOREIGN KEY (goal_id) REFERENCES g2_goals(id)
        ON DELETE CASCADE,
    CONSTRAINT g2_fk_goal_history_user
        FOREIGN KEY (user_id) REFERENCES g2_users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;