-- ============================================================
-- SaaS PLATFORM
-- DATABASE: database.sql
-- VERSION: 1.0
-- ============================================================

CREATE DATABASE IF NOT EXISTS saas_platform
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE saas_platform;

SET FOREIGN_KEY_CHECKS = 0;


-- ============================================================
-- 1. CONFIGURACIÓN GENERAL
-- ============================================================

CREATE TABLE settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- ============================================================
-- 2. ADMINISTRADORES
-- ============================================================

CREATE TABLE admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    full_name VARCHAR(150) NOT NULL,

    email VARCHAR(190) NOT NULL UNIQUE,

    password_hash VARCHAR(255) NOT NULL,

    avatar VARCHAR(255) NULL,

    role ENUM(
        'super_admin',
        'admin',
        'support'
    ) NOT NULL DEFAULT 'admin',

    status ENUM(
        'active',
        'inactive',
        'suspended'
    ) NOT NULL DEFAULT 'active',

    last_login_at DATETIME NULL,

    last_login_ip VARCHAR(45) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_admin_status (status),
    INDEX idx_admin_role (role)

) ENGINE=InnoDB;


-- ============================================================
-- 3. CLIENTES / TENANTS
-- ============================================================

CREATE TABLE tenants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    uuid CHAR(36) NOT NULL UNIQUE,

    full_name VARCHAR(150) NOT NULL,

    business_name VARCHAR(190) NULL,

    email VARCHAR(190) NULL,

    phone VARCHAR(40) NULL,

    avatar VARCHAR(255) NULL,

    logo VARCHAR(255) NULL,

    country VARCHAR(100) DEFAULT 'Colombia',

    country_code CHAR(2) DEFAULT 'CO',

    currency VARCHAR(10) DEFAULT 'COP',

    timezone VARCHAR(100) DEFAULT 'America/Bogota',

    language VARCHAR(10) DEFAULT 'es',

    primary_color VARCHAR(20) DEFAULT '#635BFF',

    secondary_color VARCHAR(20) DEFAULT '#3B82F6',

    status ENUM(
        'active',
        'inactive',
        'suspended',
        'pending'
    ) NOT NULL DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_tenant_status (status),

    INDEX idx_tenant_email (email)

) ENGINE=InnoDB;


-- ============================================================
-- 4. TOKENS PÚBLICOS DE CLIENTES
-- ============================================================

CREATE TABLE tenant_tokens (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    token VARCHAR(20) NOT NULL UNIQUE,

    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_token_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    INDEX idx_token_tenant (tenant_id),

    INDEX idx_token_active (is_active)

) ENGINE=InnoDB;


-- ============================================================
-- 5. USUARIOS DE CLIENTES
-- ============================================================

CREATE TABLE users (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    full_name VARCHAR(150) NOT NULL,

    email VARCHAR(190) NOT NULL,

    password_hash VARCHAR(255) NOT NULL,

    avatar VARCHAR(255) NULL,

    phone VARCHAR(40) NULL,

    role ENUM(
        'owner',
        'admin',
        'member',
        'viewer'
    ) NOT NULL DEFAULT 'owner',

    status ENUM(
        'active',
        'inactive',
        'suspended',
        'pending'
    ) NOT NULL DEFAULT 'active',

    email_verified_at DATETIME NULL,

    last_login_at DATETIME NULL,

    last_login_ip VARCHAR(45) NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    UNIQUE KEY unique_tenant_email (
        tenant_id,
        email
    ),

    INDEX idx_user_tenant (tenant_id),

    INDEX idx_user_status (status)

) ENGINE=InnoDB;


-- ============================================================
-- 6. PLANES
-- ============================================================

CREATE TABLE plans (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    slug VARCHAR(100) NOT NULL UNIQUE,

    description TEXT NULL,

    price DECIMAL(12,2) NOT NULL DEFAULT 0,

    billing_cycle ENUM(
        'monthly',
        'yearly',
        'lifetime',
        'custom'
    ) NOT NULL DEFAULT 'monthly',

    max_users INT UNSIGNED DEFAULT 1,

    max_events INT UNSIGNED NULL,

    max_tasks INT UNSIGNED NULL,

    max_notes INT UNSIGNED NULL,

    max_accounts INT UNSIGNED NULL,

    max_storage_mb INT UNSIGNED NULL,

    is_free BOOLEAN DEFAULT FALSE,

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB;


-- ============================================================
-- 7. MÓDULOS
-- ============================================================

CREATE TABLE modules (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100) NOT NULL,

    slug VARCHAR(100) NOT NULL UNIQUE,

    description TEXT NULL,

    icon VARCHAR(100) NULL,

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB;


-- ============================================================
-- 8. MÓDULOS DISPONIBLES POR PLAN
-- ============================================================

CREATE TABLE plan_modules (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    plan_id BIGINT UNSIGNED NOT NULL,

    module_id BIGINT UNSIGNED NOT NULL,

    CONSTRAINT fk_plan_module_plan
        FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_plan_module_module
        FOREIGN KEY (module_id)
        REFERENCES modules(id)
        ON DELETE CASCADE,

    UNIQUE KEY unique_plan_module (
        plan_id,
        module_id
    )

) ENGINE=InnoDB;


-- ============================================================
-- 9. SUSCRIPCIONES
-- ============================================================

CREATE TABLE subscriptions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    plan_id BIGINT UNSIGNED NOT NULL,

    status ENUM(
        'active',
        'pending',
        'expired',
        'cancelled',
        'suspended'
    ) NOT NULL DEFAULT 'active',

    starts_at DATETIME NOT NULL,

    ends_at DATETIME NULL,

    auto_renew BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_subscription_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_subscription_plan
        FOREIGN KEY (plan_id)
        REFERENCES plans(id)

) ENGINE=InnoDB;


-- ============================================================
-- 10. CATEGORÍAS DE EVENTOS
-- ============================================================

CREATE TABLE event_categories (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NULL,

    name VARCHAR(100) NOT NULL,

    color VARCHAR(20) NOT NULL DEFAULT '#635BFF',

    icon VARCHAR(100) NULL,

    is_default BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_event_category_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    INDEX idx_event_category_tenant (tenant_id)

) ENGINE=InnoDB;


-- ============================================================
-- 11. EVENTOS / CALENDARIO
-- ============================================================

CREATE TABLE events (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    user_id BIGINT UNSIGNED NULL,

    category_id BIGINT UNSIGNED NULL,

    title VARCHAR(190) NOT NULL,

    description TEXT NULL,

    location VARCHAR(255) NULL,

    start_at DATETIME NOT NULL,

    end_at DATETIME NULL,

    all_day BOOLEAN DEFAULT FALSE,

    color VARCHAR(20) NULL,

    priority ENUM(
        'low',
        'normal',
        'high',
        'urgent'
    ) DEFAULT 'normal',

    status ENUM(
        'scheduled',
        'in_progress',
        'completed',
        'cancelled'
    ) DEFAULT 'scheduled',

    recurrence_type ENUM(
        'none',
        'daily',
        'weekly',
        'monthly',
        'yearly',
        'custom'
    ) DEFAULT 'none',

    recurrence_rule TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_event_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_event_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_event_category
        FOREIGN KEY (category_id)
        REFERENCES event_categories(id)
        ON DELETE SET NULL,

    INDEX idx_event_tenant_date (
        tenant_id,
        start_at
    )

) ENGINE=InnoDB;


-- ============================================================
-- 12. RECORDATORIOS
-- ============================================================

CREATE TABLE event_reminders (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    event_id BIGINT UNSIGNED NOT NULL,

    minutes_before INT UNSIGNED NOT NULL,

    reminder_type ENUM(
        'system',
        'email',
        'push'
    ) DEFAULT 'system',

    is_sent BOOLEAN DEFAULT FALSE,

    sent_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_reminder_event
        FOREIGN KEY (event_id)
        REFERENCES events(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 13. FESTIVOS
-- ============================================================

CREATE TABLE holidays (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    country_code CHAR(2) NOT NULL DEFAULT 'CO',

    name VARCHAR(190) NOT NULL,

    holiday_date DATE NOT NULL,

    year SMALLINT UNSIGNED NOT NULL,

    description TEXT NULL,

    is_national BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_holiday (
        country_code,
        holiday_date
    ),

    INDEX idx_holiday_year (
        country_code,
        year
    )

) ENGINE=InnoDB;


-- ============================================================
-- 14. TAREAS
-- ============================================================

CREATE TABLE tasks (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    user_id BIGINT UNSIGNED NULL,

    title VARCHAR(190) NOT NULL,

    description TEXT NULL,

    due_at DATETIME NULL,

    priority ENUM(
        'low',
        'normal',
        'high',
        'urgent'
    ) DEFAULT 'normal',

    status ENUM(
        'pending',
        'in_progress',
        'completed',
        'cancelled'
    ) DEFAULT 'pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_task_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_task_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_task_tenant_status (
        tenant_id,
        status
    )

) ENGINE=InnoDB;


-- ============================================================
-- 15. NOTAS
-- ============================================================

CREATE TABLE notes (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    user_id BIGINT UNSIGNED NULL,

    title VARCHAR(190) NOT NULL,

    content LONGTEXT NULL,

    color VARCHAR(20) DEFAULT '#635BFF',

    is_pinned BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_note_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_note_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL

) ENGINE=InnoDB;


-- ============================================================
-- 16. CUENTAS FINANCIERAS
-- ============================================================

CREATE TABLE financial_accounts (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    name VARCHAR(150) NOT NULL,

    type ENUM(
        'cash',
        'bank',
        'credit_card',
        'savings',
        'investment',
        'other'
    ) NOT NULL DEFAULT 'other',

    initial_balance DECIMAL(14,2) DEFAULT 0,

    current_balance DECIMAL(14,2) DEFAULT 0,

    currency VARCHAR(10) DEFAULT 'COP',

    color VARCHAR(20) DEFAULT '#635BFF',

    is_active BOOLEAN DEFAULT TRUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_financial_account_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 17. CATEGORÍAS FINANCIERAS
-- ============================================================

CREATE TABLE financial_categories (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NULL,

    name VARCHAR(100) NOT NULL,

    type ENUM(
        'income',
        'expense'
    ) NOT NULL,

    icon VARCHAR(100) NULL,

    color VARCHAR(20) DEFAULT '#635BFF',

    is_default BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_financial_category_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 18. MOVIMIENTOS FINANCIEROS
-- ============================================================

CREATE TABLE transactions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    account_id BIGINT UNSIGNED NOT NULL,

    category_id BIGINT UNSIGNED NULL,

    user_id BIGINT UNSIGNED NULL,

    type ENUM(
        'income',
        'expense',
        'transfer'
    ) NOT NULL,

    amount DECIMAL(14,2) NOT NULL,

    description VARCHAR(255) NOT NULL,

    transaction_date DATE NOT NULL,

    notes TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_transaction_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_transaction_account
        FOREIGN KEY (account_id)
        REFERENCES financial_accounts(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_transaction_category
        FOREIGN KEY (category_id)
        REFERENCES financial_categories(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_transaction_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL,

    INDEX idx_transaction_date (
        tenant_id,
        transaction_date
    )

) ENGINE=InnoDB;


-- ============================================================
-- 19. PRESUPUESTOS
-- ============================================================

CREATE TABLE budgets (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    category_id BIGINT UNSIGNED NULL,

    name VARCHAR(150) NOT NULL,

    amount DECIMAL(14,2) NOT NULL,

    period ENUM(
        'monthly',
        'yearly',
        'custom'
    ) DEFAULT 'monthly',

    start_date DATE NOT NULL,

    end_date DATE NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_budget_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_budget_category
        FOREIGN KEY (category_id)
        REFERENCES financial_categories(id)
        ON DELETE SET NULL

) ENGINE=InnoDB;


-- ============================================================
-- 20. METAS FINANCIERAS
-- ============================================================

CREATE TABLE financial_goals (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    name VARCHAR(150) NOT NULL,

    target_amount DECIMAL(14,2) NOT NULL,

    current_amount DECIMAL(14,2) DEFAULT 0,

    target_date DATE NULL,

    description TEXT NULL,

    status ENUM(
        'active',
        'completed',
        'cancelled'
    ) DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_goal_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 21. NOTIFICACIONES
-- ============================================================

CREATE TABLE notifications (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    user_id BIGINT UNSIGNED NULL,

    type VARCHAR(50) NOT NULL,

    title VARCHAR(190) NOT NULL,

    message TEXT NOT NULL,

    reference_type VARCHAR(50) NULL,

    reference_id BIGINT UNSIGNED NULL,

    is_read BOOLEAN DEFAULT FALSE,

    read_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_notification_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_notification_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_notification_user (
        user_id,
        is_read
    )

) ENGINE=InnoDB;


-- ============================================================
-- 22. SESIONES DE USUARIO
-- ============================================================

CREATE TABLE user_sessions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    user_id BIGINT UNSIGNED NOT NULL,

    session_token VARCHAR(255) NOT NULL UNIQUE,

    ip_address VARCHAR(45) NULL,

    user_agent TEXT NULL,

    expires_at DATETIME NOT NULL,

    last_activity_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_session_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 23. SESIONES ADMINISTRATIVAS
-- ============================================================

CREATE TABLE admin_sessions (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    admin_id BIGINT UNSIGNED NOT NULL,

    session_token VARCHAR(255) NOT NULL UNIQUE,

    ip_address VARCHAR(45) NULL,

    user_agent TEXT NULL,

    expires_at DATETIME NOT NULL,

    last_activity_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_admin_session
        FOREIGN KEY (admin_id)
        REFERENCES admins(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 24. INTENTOS DE LOGIN
-- ============================================================

CREATE TABLE login_attempts (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    email VARCHAR(190) NULL,

    ip_address VARCHAR(45) NULL,

    user_type ENUM(
        'admin',
        'user'
    ) NOT NULL,

    success BOOLEAN DEFAULT FALSE,

    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_login_email (
        email
    ),

    INDEX idx_login_ip (
        ip_address
    )

) ENGINE=InnoDB;


-- ============================================================
-- 25. AUDITORÍA
-- ============================================================

CREATE TABLE audit_logs (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    actor_type ENUM(
        'admin',
        'user',
        'system'
    ) NOT NULL,

    actor_id BIGINT UNSIGNED NULL,

    tenant_id BIGINT UNSIGNED NULL,

    action VARCHAR(100) NOT NULL,

    description TEXT NULL,

    ip_address VARCHAR(45) NULL,

    user_agent TEXT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_audit_tenant (
        tenant_id
    ),

    INDEX idx_audit_actor (
        actor_type,
        actor_id
    ),

    INDEX idx_audit_date (
        created_at
    )

) ENGINE=InnoDB;


-- ============================================================
-- 26. COMUNICACIONES ADMINISTRATIVAS
-- ============================================================

CREATE TABLE communications (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(190) NOT NULL,

    message TEXT NOT NULL,

    type ENUM(
        'announcement',
        'maintenance',
        'promotion',
        'system'
    ) DEFAULT 'announcement',

    target ENUM(
        'all',
        'active',
        'specific_plan'
    ) DEFAULT 'all',

    plan_id BIGINT UNSIGNED NULL,

    is_active BOOLEAN DEFAULT TRUE,

    starts_at DATETIME NULL,

    ends_at DATETIME NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_communication_plan
        FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE SET NULL

) ENGINE=InnoDB;


-- ============================================================
-- 27. SOPORTE
-- ============================================================

CREATE TABLE support_tickets (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    tenant_id BIGINT UNSIGNED NOT NULL,

    user_id BIGINT UNSIGNED NULL,

    subject VARCHAR(190) NOT NULL,

    message TEXT NOT NULL,

    priority ENUM(
        'low',
        'normal',
        'high',
        'urgent'
    ) DEFAULT 'normal',

    status ENUM(
        'open',
        'in_progress',
        'waiting',
        'resolved',
        'closed'
    ) DEFAULT 'open',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_ticket_tenant
        FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_ticket_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL

) ENGINE=InnoDB;


-- ============================================================
-- 28. RESPUESTAS DE SOPORTE
-- ============================================================

CREATE TABLE support_replies (

    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    ticket_id BIGINT UNSIGNED NOT NULL,

    sender_type ENUM(
        'admin',
        'user'
    ) NOT NULL,

    sender_id BIGINT UNSIGNED NOT NULL,

    message TEXT NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_reply_ticket
        FOREIGN KEY (ticket_id)
        REFERENCES support_tickets(id)
        ON DELETE CASCADE

) ENGINE=InnoDB;


-- ============================================================
-- 29. DATOS INICIALES
-- ============================================================

INSERT INTO settings
(setting_key, setting_value)
VALUES
('app_name', 'Tu Plataforma'),
('app_country', 'CO'),
('app_currency', 'COP'),
('app_timezone', 'America/Bogota'),
('app_language', 'es'),
('maintenance_mode', '0');


-- ============================================================
-- PLANES
-- ============================================================

INSERT INTO plans
(
    name,
    slug,
    description,
    price,
    billing_cycle,
    max_users,
    is_free,
    is_active
)
VALUES
(
    'Gratis',
    'free',
    'Plan gratuito',
    0,
    'monthly',
    1,
    TRUE,
    TRUE
),
(
    'Profesional',
    'professional',
    'Plan profesional',
    39900,
    'monthly',
    5,
    FALSE,
    TRUE
),
(
    'Empresa',
    'business',
    'Plan empresarial',
    79900,
    'monthly',
    15,
    FALSE,
    TRUE
);


-- ============================================================
-- MÓDULOS
-- ============================================================

INSERT INTO modules
(name, slug, description, icon)
VALUES
('Inicio', 'dashboard', 'Centro de control', 'home'),
('Calendario', 'calendar', 'Calendario y eventos', 'calendar'),
('Recordatorios', 'reminders', 'Recordatorios y alertas', 'bell'),
('Tareas', 'tasks', 'Gestión de tareas', 'check-square'),
('Notas', 'notes', 'Notas personales', 'file-text'),
('Finanzas', 'finance', 'Gestión financiera', 'wallet'),
('Presupuestos', 'budgets', 'Presupuestos', 'pie-chart'),
('Metas financieras', 'financial-goals', 'Metas de ahorro', 'target'),
('Estadísticas', 'statistics', 'Estadísticas y reportes', 'bar-chart'),
('Equipos', 'teams', 'Gestión de equipos', 'users');


-- ============================================================
-- CATEGORÍAS DE EVENTOS PREDETERMINADAS
-- ============================================================

INSERT INTO event_categories
(
    tenant_id,
    name,
    color,
    icon,
    is_default
)
VALUES
(NULL, 'Personal', '#635BFF', 'user', TRUE),
(NULL, 'Trabajo', '#3B82F6', 'briefcase', TRUE),
(NULL, 'Importante', '#EF4444', 'alert-circle', TRUE),
(NULL, 'Finanzas', '#10B981', 'wallet', TRUE),
(NULL, 'Familia', '#EC4899', 'heart', TRUE),
(NULL, 'General', '#64748B', 'calendar', TRUE);


-- ============================================================
-- CATEGORÍAS FINANCIERAS
-- ============================================================

INSERT INTO financial_categories
(
    tenant_id,
    name,
    type,
    icon,
    color,
    is_default
)
VALUES
(NULL, 'Salario', 'income', 'briefcase', '#10B981', TRUE),
(NULL, 'Ventas', 'income', 'trending-up', '#22C55E', TRUE),
(NULL, 'Otros ingresos', 'income', 'plus-circle', '#14B8A6', TRUE),

(NULL, 'Alimentación', 'expense', 'utensils', '#F59E0B', TRUE),
(NULL, 'Hogar', 'expense', 'home', '#3B82F6', TRUE),
(NULL, 'Transporte', 'expense', 'car', '#8B5CF6', TRUE),
(NULL, 'Servicios', 'expense', 'zap', '#EF4444', TRUE),
(NULL, 'Educación', 'expense', 'book', '#06B6D4', TRUE),
(NULL, 'Entretenimiento', 'expense', 'film', '#EC4899', TRUE),
(NULL, 'Compras', 'expense', 'shopping-bag', '#F97316', TRUE),
(NULL, 'Deudas', 'expense', 'credit-card', '#DC2626', TRUE),
(NULL, 'Otros gastos', 'expense', 'more-horizontal', '#64748B', TRUE);


-- ============================================================
-- ADMINISTRADOR PRINCIPAL
-- ============================================================
--
-- IMPORTANTE:
-- Reemplazar el HASH por uno generado mediante:
--
-- password_hash('TU_CONTRASEÑA', PASSWORD_DEFAULT)
--
-- NO colocar la contraseña directamente aquí.
--

INSERT INTO admins
(
    full_name,
    email,
    password_hash,
    role,
    status
)
VALUES
(
    'Administrador Principal',
    'TU_CORREO_ADMIN',
    'REEMPLAZAR_CON_PASSWORD_HASH',
    'super_admin',
    'active'
);


-- ============================================================
-- FINAL
-- ============================================================

SET FOREIGN_KEY_CHECKS = 1;