-- ============================================================
-- QUERY COMPLETA - Migrations de 2026_02_25 até 2026_02_27
-- Executar em sequência no banco de dados
-- ============================================================

-- ============================================================
-- 1) 2026_02_25_000001 - Delivery fields (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `customer_edit_bairro` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `customer_edit_cidade` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `delivery_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `pickup_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `dine_in_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `delivery_payment_dinheiro` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `delivery_payment_cartao` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `delivery_ask_card_brand` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `delivery_payment_brands` JSON NULL,
    ADD COLUMN `pix_cpf` VARCHAR(255) NULL,
    ADD COLUMN `pix_cnpj` VARCHAR(255) NULL,
    ADD COLUMN `pix_no_cnpj_account` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `pix_key_type` VARCHAR(255) NULL,
    ADD COLUMN `pix_key` VARCHAR(255) NULL,
    ADD COLUMN `pix_phone` VARCHAR(255) NULL,
    ADD COLUMN `pix_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `credit_card_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `credit_card_repasse` VARCHAR(255) NOT NULL DEFAULT '1_dia';

-- ============================================================
-- 2) 2026_02_25_000002 - Waiter app settings (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `waiter_delivery_orders` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_edit_orders` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_edit_extra_charges` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_cancel_orders` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_show_order_status` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_open_tab_no_order` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_auto_print_orders` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `waiter_auto_print_closing` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_close_account` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `waiter_apply_discount` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_apply_surcharge` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_ask_people_count` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_transfer_tables` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `waiter_transfer_occupied` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `waiter_customer_call` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `waiter_show_idle_tables` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `waiter_idle_time` INT NOT NULL DEFAULT 30;

-- ============================================================
-- 3) 2026_02_26_000001 - Comanda settings (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `comanda_link_table` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_ask_cpf` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_open_no_order` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_min_consumption` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_min_consumption_value` DECIMAL(10,2) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_couvert` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_couvert_value` DECIMAL(10,2) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_entrance_fee` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `comanda_entrance_fee_value` DECIMAL(10,2) NOT NULL DEFAULT 0;

-- ============================================================
-- 4) 2026_02_27_000001 - PDV appearance settings (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `pdv_start_with_items` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `pdv_show_category_sidebar` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN `pdv_fullscreen` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `pdv_hide_out_of_stock` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `pdv_display_mode` VARCHAR(255) NOT NULL DEFAULT 'column',
    ADD COLUMN `pdv_default_order_type` VARCHAR(255) NOT NULL DEFAULT 'balcao',
    ADD COLUMN `pdv_ask_customer` TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN `pdv_ask_cpf` TINYINT(1) NOT NULL DEFAULT 0;

-- ============================================================
-- 5) 2026_02_27_000002 - Service fee (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `service_fee_enabled` TINYINT(1) NOT NULL DEFAULT 0;

-- ============================================================
-- 6) 2026_02_27_000003 - Sales recovery (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `sales_recovery_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `service_fee_enabled`;

-- ============================================================
-- 7) 2026_02_27_000004 - Cashback settings (tenant_settings)
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `cashback_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `sales_recovery_enabled`,
    ADD COLUMN `cashback_mode` VARCHAR(255) NOT NULL DEFAULT 'simple' AFTER `cashback_enabled`,
    ADD COLUMN `cashback_percentage` DECIMAL(5,2) NOT NULL DEFAULT 5.00 AFTER `cashback_mode`,
    ADD COLUMN `cashback_expiration_days` INT NOT NULL DEFAULT 15 AFTER `cashback_percentage`,
    ADD COLUMN `cashback_weekday_percentages` JSON NULL AFTER `cashback_expiration_days`;

-- ============================================================
-- 8) 2026_02_27_000005 - Extra fields coupons
-- ============================================================
ALTER TABLE `coupons`
    ADD COLUMN `discount_on` ENUM('product', 'shipping') NOT NULL DEFAULT 'product' AFTER `description`,
    ADD COLUMN `new_customer_only` TINYINT(1) NOT NULL DEFAULT 0 AFTER `max_uses_per_customer`,
    ADD COLUMN `usage_mode` ENUM('unlimited', 'limited', 'select_customer') NOT NULL DEFAULT 'unlimited' AFTER `new_customer_only`,
    ADD COLUMN `max_redemptions` INT NOT NULL DEFAULT 1 AFTER `usage_mode`,
    ADD COLUMN `available_days` JSON NULL AFTER `expires_at`,
    ADD COLUMN `no_expiration` TINYINT(1) NOT NULL DEFAULT 1 AFTER `available_days`,
    ADD COLUMN `bot_send` TINYINT(1) NOT NULL DEFAULT 0 AFTER `no_expiration`,
    ADD COLUMN `visible` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bot_send`;

-- ============================================================
-- 9) 2026_02_27_000006 - Create promotions table
-- ============================================================
CREATE TABLE `promotions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `min_order_value` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `category_ids` JSON NULL COMMENT 'IDs das categorias participantes',
    `reward_type` ENUM('product', 'shipping') NOT NULL DEFAULT 'product',
    `discount_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0,
    `reward_product_ids` JSON NULL COMMENT 'IDs dos produtos de recompensa',
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `promotions_tenant_id_active_index` (`tenant_id`, `active`),
    CONSTRAINT `promotions_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10) 2026_02_27_000007 - Satisfaction settings + questions table
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `satisfaction_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `sales_recovery_enabled`,
    ADD COLUMN `satisfaction_frequency` INT NOT NULL DEFAULT 4 AFTER `satisfaction_enabled`;

CREATE TABLE `satisfaction_questions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT UNSIGNED NOT NULL,
    `question` VARCHAR(255) NOT NULL,
    `category` VARCHAR(255) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `satisfaction_questions_tenant_id_active_index` (`tenant_id`, `active`),
    CONSTRAINT `satisfaction_questions_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11) Colunas faltantes - timezone, availability_mode, unavailability_periods
-- ============================================================
ALTER TABLE `tenant_settings`
    ADD COLUMN `timezone` VARCHAR(255) NOT NULL DEFAULT 'America/Sao_Paulo',
    ADD COLUMN `availability_mode` VARCHAR(255) NOT NULL DEFAULT 'specific_hours',
    ADD COLUMN `unavailability_periods` JSON NULL;

-- ============================================================
-- 12) Tabela discount_percentage em categories (2026_02_27_000008)
-- ============================================================
ALTER TABLE `categories`
    ADD COLUMN `discount_percentage` DECIMAL(5,2) NULL DEFAULT NULL;

-- ============================================================
-- 13) Tabelas partners e partner_categories (2026_02_27_000009)
-- ============================================================
CREATE TABLE `partner_categories` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `icon` VARCHAR(255) NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `partner_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `partners` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `partner_category_id` BIGINT UNSIGNED NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `logo` VARCHAR(255) NULL,
    `offer_title` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `discount_url` VARCHAR(255) NULL,
    `discount_code` VARCHAR(255) NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `partners_partner_category_id_foreign` FOREIGN KEY (`partner_category_id`) REFERENCES `partner_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14) Tabelas do sistema de referrals (2026_02_27_000010)
-- ============================================================
CREATE TABLE `referrals` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT UNSIGNED NOT NULL,
    `referral_code` VARCHAR(255) NOT NULL,
    `referred_name` VARCHAR(255) NOT NULL,
    `referred_phone` VARCHAR(255) NULL,
    `referred_email` VARCHAR(255) NULL,
    `status` ENUM('pending', 'active', 'cancelled') NOT NULL DEFAULT 'pending',
    `notes` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `referrals_referral_code_unique` (`referral_code`),
    INDEX `referrals_tenant_id_status_index` (`tenant_id`, `status`),
    CONSTRAINT `referrals_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referral_prizes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NULL,
    `required_referrals` INT NOT NULL DEFAULT 1,
    `estimated_value` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `referral_achievements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id` BIGINT UNSIGNED NOT NULL,
    `referral_prize_id` BIGINT UNSIGNED NOT NULL,
    `claimed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `referral_achievements_tenant_id_foreign` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `referral_achievements_referral_prize_id_foreign` FOREIGN KEY (`referral_prize_id`) REFERENCES `referral_prizes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FIM - Total: 14 blocos de migrations consolidadas
-- tenant_settings: +64 colunas
-- categories: +1 coluna
-- coupons: +8 colunas
-- promotions: tabela nova
-- satisfaction_questions: tabela nova
-- partner_categories: tabela nova
-- partners: tabela nova
-- referrals: tabela nova
-- referral_prizes: tabela nova
-- referral_achievements: tabela nova
-- ============================================================