-- =====================================================================
-- CobraFácil - Schema MySQL/MariaDB completo
-- Para usar SEM o Artisan migrate, basta executar este arquivo.
-- Compatível com MySQL 8+ / MariaDB 10.5+
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

-- ---------------------------------------------------------------------
-- USUÁRIOS / AUTENTICAÇÃO (nativo do Laravel)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    email_verified_at TIMESTAMP NULL,
    password VARCHAR(255) NOT NULL,
    remember_token VARCHAR(100) NULL,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
    email VARCHAR(255) PRIMARY KEY,
    token VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    payload LONGTEXT NOT NULL,
    last_activity INT NOT NULL,
    INDEX sessions_user_id_index (user_id),
    INDEX sessions_last_activity_index (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cache (
    `key` VARCHAR(255) PRIMARY KEY,
    value MEDIUMTEXT NOT NULL,
    expiration INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue VARCHAR(255) NOT NULL,
    payload LONGTEXT NOT NULL,
    attempts TINYINT UNSIGNED NOT NULL,
    reserved_at INT UNSIGNED NULL,
    available_at INT UNSIGNED NOT NULL,
    created_at INT UNSIGNED NOT NULL,
    INDEX jobs_queue_index (queue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- PLANOS (3 níveis: trial, free, pro, business)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
    id VARCHAR(50) PRIMARY KEY, -- 'trial' | 'free' | 'pro' | 'business'
    name VARCHAR(100) NOT NULL,
    price_brl DECIMAL(10,2) NOT NULL DEFAULT 0,
    max_clients INT NULL,            -- NULL = ilimitado
    max_charges_per_month INT NULL,  -- NULL = ilimitado
    trial_days INT NOT NULL DEFAULT 0,
    features JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- ASSINATURAS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    plan_id VARCHAR(50) NOT NULL,
    status ENUM('trialing','active','past_due','expired','cancelled') NOT NULL DEFAULT 'trialing',
    trial_ends_at TIMESTAMP NULL,
    current_period_start TIMESTAMP NULL,
    current_period_end TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    mp_payment_id VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX subscriptions_user_id_index (user_id),
    CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,
    plan_id VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','cancelled','expired') NOT NULL DEFAULT 'pending',
    mp_payment_id VARCHAR(255) NULL,
    pix_qr_code TEXT NULL,
    pix_qr_code_base64 LONGTEXT NULL,
    pix_ticket_url TEXT NULL,
    pix_expires_at TIMESTAMP NULL,
    paid_at TIMESTAMP NULL,
    period_start TIMESTAMP NOT NULL,
    period_end TIMESTAMP NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX subinv_user_id_index (user_id),
    CONSTRAINT fk_subinv_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subinv_sub  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- CLIENTES DO USUÁRIO
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS clients (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(255) NULL,
    document VARCHAR(30) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX clients_user_id_index (user_id),
    CONSTRAINT fk_cli_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- COBRANÇAS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS charges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED NOT NULL,
    description VARCHAR(500) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','overdue','cancelled') NOT NULL DEFAULT 'pending',
    due_date DATE NOT NULL,
    payment_link TEXT NULL,
    public_token VARCHAR(64) NULL UNIQUE,
    paid_at TIMESTAMP NULL,
    -- PIX (Mercado Pago)
    pix_payment_id VARCHAR(255) NULL,
    pix_qr_code TEXT NULL,
    pix_qr_code_base64 LONGTEXT NULL,
    pix_expires_at TIMESTAMP NULL,
    -- Recorrência
    recurrence_frequency ENUM('weekly','monthly','yearly') NULL,
    recurrence_interval INT NOT NULL DEFAULT 1,
    recurrence_end_date DATE NULL,
    recurrence_active TINYINT(1) NOT NULL DEFAULT 0,
    next_generation_date DATE NULL,
    parent_charge_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX charges_user_id_index (user_id),
    INDEX charges_client_id_index (client_id),
    INDEX charges_due_date_index (due_date),
    INDEX charges_status_index (status),
    CONSTRAINT fk_chg_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_chg_cli  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- INTEGRAÇÕES (POR USUÁRIO)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS evolution_settings (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    base_url VARCHAR(255) NOT NULL,
    api_key VARCHAR(255) NOT NULL,
    instance_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_evo_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mercadopago_settings (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    access_token VARCHAR(500) NOT NULL,
    is_sandbox TINYINT(1) NOT NULL DEFAULT 1,
    webhook_secret VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_mp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reminder_settings (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    days_before JSON NOT NULL,
    send_on_due TINYINT(1) NOT NULL DEFAULT 1,
    days_after JSON NOT NULL,
    send_hour INT NOT NULL DEFAULT 9,
    message_before TEXT NOT NULL,
    message_on_due TEXT NOT NULL,
    message_after TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_rs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- LOGS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reminder_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    charge_id BIGINT UNSIGNED NOT NULL,
    reminder_type ENUM('before_due','on_due','after_due','manual') NOT NULL,
    status ENUM('sent','failed') NOT NULL,
    message TEXT NOT NULL,
    response JSON NULL,
    error TEXT NULL,
    sent_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX rlogs_user_id_index (user_id),
    INDEX rlogs_charge_id_index (charge_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS plan_block_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    action ENUM('create_client','create_charge','send_reminder','create_pix') NOT NULL,
    reason TEXT NOT NULL,
    plan_id VARCHAR(50) NULL,
    current_count INT NULL,
    limit_value INT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX pbl_user_id_index (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- BRANDING (singleton)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_branding (
    id TINYINT(1) PRIMARY KEY DEFAULT 1,
    brand_name VARCHAR(100) NOT NULL DEFAULT 'CobraFácil',
    tagline VARCHAR(255) NULL,
    logo_url VARCHAR(500) NULL,
    favicon_url VARCHAR(500) NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_branding_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;

-- ---------------------------------------------------------------------
-- SEEDS
-- ---------------------------------------------------------------------
INSERT INTO plans (id, name, price_brl, max_clients, max_charges_per_month, trial_days, sort_order, active) VALUES
  ('trial',    'Teste Grátis (14 dias)', 0.00, NULL, NULL, 14, 0, 1),
  ('free',     'Grátis',                 0.00, 1,    1,    0,  1, 1),
  ('pro',      'Pro',                   49.90, 100,  500,  0,  2, 1),
  ('business', 'Business',             149.90, NULL, NULL, 0,  3, 1)
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO app_branding (id, brand_name, tagline) VALUES (1, 'CobraFácil', 'Cobre seus clientes pelo WhatsApp')
ON DUPLICATE KEY UPDATE brand_name=brand_name;