-- ============================================================
--  DENTAL SAAS — Schema MySQL Completo
--  Gerado automaticamente — versão MVP
--  Compatível: MySQL 8.0+ / MariaDB 10.4+
-- ============================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "-03:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- Cria o banco caso não exista
CREATE DATABASE IF NOT EXISTS `dental_saas`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `dental_saas`;

-- ============================================================
-- 1. users (tabela base do Laravel Auth)
-- ============================================================
CREATE TABLE `users` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`              VARCHAR(255)    NOT NULL,
  `email`             VARCHAR(255)    NOT NULL,
  `email_verified_at` TIMESTAMP       NULL DEFAULT NULL,
  `password`          VARCHAR(255)    NOT NULL,
  `remember_token`    VARCHAR(100)    NULL DEFAULT NULL,
  `created_at`        TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`        TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 2. clinics
-- ============================================================
CREATE TABLE `clinics` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                   VARCHAR(255)    NOT NULL,
  `cnpj`                   VARCHAR(18)     NULL DEFAULT NULL,
  `phone`                  VARCHAR(20)     NULL DEFAULT NULL,
  `email`                  VARCHAR(255)    NULL DEFAULT NULL,
  `logo_path`              VARCHAR(255)    NULL DEFAULT NULL,
  `address`                VARCHAR(255)    NULL DEFAULT NULL,
  `city`                   VARCHAR(255)    NULL DEFAULT NULL,
  `state`                  CHAR(2)         NULL DEFAULT NULL,
  `zip_code`               VARCHAR(9)      NULL DEFAULT NULL,
  `working_hours`          JSON            NULL DEFAULT NULL COMMENT 'Ex: {"seg":{"open":"08:00","close":"18:00"}}',
  `whatsapp_token`         VARCHAR(500)    NULL DEFAULT NULL,
  `whatsapp_phone_id`      VARCHAR(100)    NULL DEFAULT NULL,
  `whatsapp_active`        TINYINT(1)      NOT NULL DEFAULT 0,
  `reminder_hours_before`  INT             NOT NULL DEFAULT 24,
  `active`                 TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`             TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`             TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`             TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clinics_cnpj_unique` (`cnpj`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. professionals
-- ============================================================
CREATE TABLE `professionals` (
  `id`                            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`                     BIGINT UNSIGNED NOT NULL,
  `user_id`                       BIGINT UNSIGNED NULL DEFAULT NULL,
  `name`                          VARCHAR(255)    NOT NULL,
  `cro`                           VARCHAR(20)     NULL DEFAULT NULL,
  `specialty`                     VARCHAR(255)    NULL DEFAULT NULL,
  `phone`                         VARCHAR(20)     NULL DEFAULT NULL,
  `color`                         VARCHAR(7)      NOT NULL DEFAULT '#2196F3',
  `avatar_path`                   VARCHAR(255)    NULL DEFAULT NULL,
  `schedule`                      JSON            NULL DEFAULT NULL,
  `default_appointment_duration`  INT             NOT NULL DEFAULT 30,
  `active`                        TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`                    TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`                    TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`                    TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `professionals_clinic_active` (`clinic_id`, `active`),
  CONSTRAINT `fk_professionals_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_professionals_user`   FOREIGN KEY (`user_id`)   REFERENCES `users`   (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. patients
-- ============================================================
CREATE TABLE `patients` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`           BIGINT UNSIGNED NOT NULL,
  `name`                VARCHAR(255)    NOT NULL,
  `gender`              ENUM('M','F','O') NULL DEFAULT NULL,
  `birth_date`          DATE            NULL DEFAULT NULL,
  `cpf`                 VARCHAR(14)     NULL DEFAULT NULL,
  `rg`                  VARCHAR(20)     NULL DEFAULT NULL,
  `phone`               VARCHAR(20)     NULL DEFAULT NULL COMMENT 'Celular principal (WhatsApp)',
  `phone_alt`           VARCHAR(20)     NULL DEFAULT NULL,
  `is_foreign`          TINYINT(1)      NOT NULL DEFAULT 0,
  `email`               VARCHAR(255)    NULL DEFAULT NULL,
  `record_number`       VARCHAR(100)    NULL DEFAULT NULL,
  `profession`          VARCHAR(255)    NULL DEFAULT NULL,
  `social_media`        VARCHAR(255)    NULL DEFAULT NULL,
  `how_arrived`         ENUM('indicacao','instagram','facebook','google','outros') NULL DEFAULT NULL,
  `tags`                JSON            NULL DEFAULT NULL,
  `address`             VARCHAR(255)    NULL DEFAULT NULL,
  `address_number`      VARCHAR(20)     NULL DEFAULT NULL,
  `neighborhood`        VARCHAR(255)    NULL DEFAULT NULL,
  `city`                VARCHAR(255)    NULL DEFAULT NULL,
  `state`               CHAR(2)         NULL DEFAULT NULL,
  `zip_code`            VARCHAR(9)      NULL DEFAULT NULL,
  `guardian_name`       VARCHAR(255)    NULL DEFAULT NULL,
  `guardian_birth_date` DATE            NULL DEFAULT NULL,
  `guardian_cpf`        VARCHAR(14)     NULL DEFAULT NULL,
  `guardian_phone`      VARCHAR(20)     NULL DEFAULT NULL,
  `notes`               TEXT            NULL DEFAULT NULL,
  `insurance_name`      VARCHAR(255)    NULL DEFAULT NULL,
  `insurance_card_number` VARCHAR(100)  NULL DEFAULT NULL,
  `active`              TINYINT(1)      NOT NULL DEFAULT 1,
  `last_appointment_at` TIMESTAMP       NULL DEFAULT NULL,
  `created_at`          TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`          TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`          TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `patients_clinic_record_unique` (`clinic_id`, `record_number`),
  KEY `patients_clinic_name`  (`clinic_id`, `name`),
  KEY `patients_clinic_cpf`   (`clinic_id`, `cpf`),
  KEY `patients_clinic_phone` (`clinic_id`, `phone`),
  CONSTRAINT `fk_patients_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. anamnesis_templates
-- ============================================================
CREATE TABLE `anamnesis_templates` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`  BIGINT UNSIGNED NOT NULL,
  `name`       VARCHAR(255)    NOT NULL,
  `is_default` TINYINT(1)      NOT NULL DEFAULT 0,
  `questions`  JSON            NOT NULL,
  `active`     TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP       NULL DEFAULT NULL,
  `updated_at` TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_anamnesis_templates_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. anamneses
-- ============================================================
CREATE TABLE `anamneses` (
  `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id`            BIGINT UNSIGNED NOT NULL,
  `clinic_id`             BIGINT UNSIGNED NOT NULL,
  `anamnesis_template_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `professional_id`       BIGINT UNSIGNED NULL DEFAULT NULL,
  `answers`               JSON            NOT NULL,
  `general_notes`         TEXT            NULL DEFAULT NULL,
  `patient_signed`        TINYINT(1)      NOT NULL DEFAULT 0,
  `signed_at`             TIMESTAMP       NULL DEFAULT NULL,
  `created_at`            TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`            TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`            TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `anamneses_patient_clinic` (`patient_id`, `clinic_id`),
  CONSTRAINT `fk_anamneses_patient`   FOREIGN KEY (`patient_id`)            REFERENCES `patients`            (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_anamneses_clinic`    FOREIGN KEY (`clinic_id`)             REFERENCES `clinics`             (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_anamneses_template`  FOREIGN KEY (`anamnesis_template_id`) REFERENCES `anamnesis_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_anamneses_prof`      FOREIGN KEY (`professional_id`)       REFERENCES `professionals`       (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. chairs
-- ============================================================
CREATE TABLE `chairs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`  BIGINT UNSIGNED NOT NULL,
  `name`       VARCHAR(255)    NOT NULL,
  `color`      VARCHAR(7)      NOT NULL DEFAULT '#4CAF50',
  `active`     TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP       NULL DEFAULT NULL,
  `updated_at` TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_chairs_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. procedures
-- ============================================================
CREATE TABLE `procedures` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`        BIGINT UNSIGNED NOT NULL,
  `name`             VARCHAR(255)    NOT NULL,
  `description`      TEXT            NULL DEFAULT NULL,
  `default_duration` INT             NOT NULL DEFAULT 30 COMMENT 'Minutos',
  `default_price`    DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `color`            VARCHAR(7)      NOT NULL DEFAULT '#9C27B0',
  `active`           TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`       TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`       TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_procedures_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. appointments
-- ============================================================
CREATE TABLE `appointments` (
  `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`              BIGINT UNSIGNED NOT NULL,
  `patient_id`             BIGINT UNSIGNED NOT NULL,
  `professional_id`        BIGINT UNSIGNED NOT NULL,
  `chair_id`               BIGINT UNSIGNED NULL DEFAULT NULL,
  `procedure_id`           BIGINT UNSIGNED NULL DEFAULT NULL,
  `starts_at`              DATETIME        NOT NULL,
  `ends_at`                DATETIME        NOT NULL,
  `duration_minutes`       INT             NOT NULL,
  `status`                 ENUM('agendado','confirmado','em_atendimento','realizado','faltou','cancelado') NOT NULL DEFAULT 'agendado',
  `confirmed_at`           TIMESTAMP       NULL DEFAULT NULL,
  `checked_in_at`          TIMESTAMP       NULL DEFAULT NULL,
  `finished_at`            TIMESTAMP       NULL DEFAULT NULL,
  `cancelled_at`           TIMESTAMP       NULL DEFAULT NULL,
  `cancellation_reason`    VARCHAR(500)    NULL DEFAULT NULL,
  `reminder_sent`          TINYINT(1)      NOT NULL DEFAULT 0,
  `reminder_sent_at`       TIMESTAMP       NULL DEFAULT NULL,
  `reminder_status`        ENUM('pending','sent','failed','skipped') NOT NULL DEFAULT 'pending',
  `notes`                  TEXT            NULL DEFAULT NULL,
  `price`                  DECIMAL(10,2)   NULL DEFAULT NULL,
  `source`                 ENUM('manual','online','whatsapp') NOT NULL DEFAULT 'manual',
  `parent_appointment_id`  BIGINT UNSIGNED NULL DEFAULT NULL,
  `created_at`             TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`             TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`             TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `appointments_agenda`       (`clinic_id`, `starts_at`, `status`),
  KEY `appointments_professional` (`professional_id`, `starts_at`),
  KEY `appointments_patient`      (`patient_id`, `starts_at`),
  KEY `appointments_reminder`     (`reminder_sent`, `starts_at`, `status`),
  CONSTRAINT `fk_appointments_clinic`   FOREIGN KEY (`clinic_id`)             REFERENCES `clinics`       (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_appointments_patient`  FOREIGN KEY (`patient_id`)            REFERENCES `patients`      (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_appointments_prof`     FOREIGN KEY (`professional_id`)       REFERENCES `professionals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_appointments_chair`    FOREIGN KEY (`chair_id`)              REFERENCES `chairs`        (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_appointments_proc`     FOREIGN KEY (`procedure_id`)          REFERENCES `procedures`    (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_appointments_parent`   FOREIGN KEY (`parent_appointment_id`) REFERENCES `appointments`  (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. patient_documents
-- ============================================================
CREATE TABLE `patient_documents` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `patient_id`      BIGINT UNSIGNED NOT NULL,
  `clinic_id`       BIGINT UNSIGNED NOT NULL,
  `professional_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `appointment_id`  BIGINT UNSIGNED NULL DEFAULT NULL,
  `type`            ENUM('atestado','receita_simples','receita_especial','laudo','declaracao','outro') NOT NULL,
  `title`           VARCHAR(255)    NOT NULL,
  `content`         LONGTEXT        NOT NULL,
  `pdf_path`        VARCHAR(500)    NULL DEFAULT NULL,
  `issued_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at`      TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`      TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`      TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient_documents_type` (`patient_id`, `type`),
  CONSTRAINT `fk_patient_docs_patient` FOREIGN KEY (`patient_id`)     REFERENCES `patients`      (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_patient_docs_clinic`  FOREIGN KEY (`clinic_id`)      REFERENCES `clinics`       (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_patient_docs_prof`    FOREIGN KEY (`professional_id`) REFERENCES `professionals` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_patient_docs_appt`    FOREIGN KEY (`appointment_id`) REFERENCES `appointments`  (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 11. cash_registers
-- ============================================================
CREATE TABLE `cash_registers` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`       BIGINT UNSIGNED NOT NULL,
  `user_id`         BIGINT UNSIGNED NOT NULL,
  `date`            DATE            NOT NULL,
  `opening_balance` DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `closing_balance` DECIMAL(10,2)   NULL DEFAULT NULL,
  `status`          ENUM('open','closed') NOT NULL DEFAULT 'open',
  `opened_at`       TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `closed_at`       TIMESTAMP       NULL DEFAULT NULL,
  `notes`           TEXT            NULL DEFAULT NULL,
  `created_at`      TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`      TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cash_registers_clinic_date_unique` (`clinic_id`, `date`),
  KEY `cash_registers_status` (`clinic_id`, `status`),
  CONSTRAINT `fk_cash_registers_clinic` FOREIGN KEY (`clinic_id`) REFERENCES `clinics` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cash_registers_user`   FOREIGN KEY (`user_id`)   REFERENCES `users`   (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. transactions
-- ============================================================
CREATE TABLE `transactions` (
  `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`          BIGINT UNSIGNED NOT NULL,
  `cash_register_id`   BIGINT UNSIGNED NULL DEFAULT NULL,
  `patient_id`         BIGINT UNSIGNED NULL DEFAULT NULL,
  `appointment_id`     BIGINT UNSIGNED NULL DEFAULT NULL,
  `user_id`            BIGINT UNSIGNED NOT NULL,
  `professional_id`    BIGINT UNSIGNED NULL DEFAULT NULL,
  `type`               ENUM('income','expense') NOT NULL,
  `category`           ENUM('consulta','procedimento','plano_odontologico','material','equipamento','salario','aluguel','servico','imposto','outros') NOT NULL,
  `description`        VARCHAR(500)    NOT NULL,
  `amount`             DECIMAL(10,2)   NOT NULL,
  `due_date`           DATE            NOT NULL,
  `paid_date`          DATE            NULL DEFAULT NULL,
  `payment_method`     ENUM('dinheiro','pix','cartao_credito','cartao_debito','boleto','convenio','transferencia','cheque') NULL DEFAULT NULL,
  `installments_count` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `installment_number` TINYINT UNSIGNED NOT NULL DEFAULT 1,
  `status`             ENUM('pendente','pago','cancelado','estornado') NOT NULL DEFAULT 'pendente',
  `notes`              TEXT            NULL DEFAULT NULL,
  `batch_id`           VARCHAR(36)     NULL DEFAULT NULL COMMENT 'UUID para agrupar parcelas',
  `created_at`         TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`         TIMESTAMP       NULL DEFAULT NULL,
  `deleted_at`         TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transactions_type_status`  (`clinic_id`, `type`, `status`),
  KEY `transactions_due_date`     (`clinic_id`, `due_date`),
  KEY `transactions_paid_date`    (`clinic_id`, `paid_date`),
  KEY `transactions_patient`      (`patient_id`, `status`),
  KEY `transactions_batch`        (`batch_id`),
  CONSTRAINT `fk_transactions_clinic`    FOREIGN KEY (`clinic_id`)        REFERENCES `clinics`        (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_transactions_cash`      FOREIGN KEY (`cash_register_id`) REFERENCES `cash_registers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transactions_patient`   FOREIGN KEY (`patient_id`)       REFERENCES `patients`       (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transactions_appt`      FOREIGN KEY (`appointment_id`)   REFERENCES `appointments`   (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_transactions_user`      FOREIGN KEY (`user_id`)          REFERENCES `users`          (`id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_transactions_prof`      FOREIGN KEY (`professional_id`)  REFERENCES `professionals`  (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 13. whatsapp_logs
-- ============================================================
CREATE TABLE `whatsapp_logs` (
  `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `clinic_id`           BIGINT UNSIGNED NOT NULL,
  `patient_id`          BIGINT UNSIGNED NULL DEFAULT NULL,
  `appointment_id`      BIGINT UNSIGNED NULL DEFAULT NULL,
  `to_phone`            VARCHAR(20)     NOT NULL,
  `message_type`        ENUM('reminder_24h','reminder_2h','confirmation_request','custom') NOT NULL,
  `message_body`        TEXT            NOT NULL,
  `external_message_id` VARCHAR(255)    NULL DEFAULT NULL,
  `status`              ENUM('queued','sent','delivered','read','failed') NOT NULL DEFAULT 'queued',
  `error_message`       TEXT            NULL DEFAULT NULL,
  `attempts`            INT             NOT NULL DEFAULT 0,
  `sent_at`             TIMESTAMP       NULL DEFAULT NULL,
  `delivered_at`        TIMESTAMP       NULL DEFAULT NULL,
  `read_at`             TIMESTAMP       NULL DEFAULT NULL,
  `created_at`          TIMESTAMP       NULL DEFAULT NULL,
  `updated_at`          TIMESTAMP       NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `whatsapp_logs_clinic_status` (`clinic_id`, `status`),
  KEY `whatsapp_logs_appt_type`     (`appointment_id`, `message_type`),
  CONSTRAINT `fk_whatsapp_logs_clinic` FOREIGN KEY (`clinic_id`)     REFERENCES `clinics`      (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_whatsapp_logs_patient` FOREIGN KEY (`patient_id`)   REFERENCES `patients`     (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_whatsapp_logs_appt`    FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Tabela de jobs para filas do Laravel (required)
-- ============================================================
CREATE TABLE `jobs` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue`        VARCHAR(255)    NOT NULL,
  `payload`      LONGTEXT        NOT NULL,
  `attempts`     TINYINT UNSIGNED NOT NULL,
  `reserved_at`  INT UNSIGNED    NULL DEFAULT NULL,
  `available_at` INT UNSIGNED    NOT NULL,
  `created_at`   INT UNSIGNED    NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `failed_jobs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid`       VARCHAR(255)    NOT NULL,
  `connection` TEXT            NOT NULL,
  `queue`      TEXT            NOT NULL,
  `payload`    LONGTEXT        NOT NULL,
  `exception`  LONGTEXT        NOT NULL,
  `failed_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `job_batches` (
  `id`             VARCHAR(255)    NOT NULL,
  `name`           VARCHAR(255)    NOT NULL,
  `total_jobs`     INT             NOT NULL,
  `pending_jobs`   INT             NOT NULL,
  `failed_jobs`    INT             NOT NULL,
  `failed_job_ids` LONGTEXT        NOT NULL,
  `options`        MEDIUMTEXT      NULL DEFAULT NULL,
  `cancelled_at`   INT             NULL DEFAULT NULL,
  `created_at`     INT             NOT NULL,
  `finished_at`    INT             NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Laravel migrations table (controle interno)
-- ============================================================
CREATE TABLE `migrations` (
  `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch`     INT          NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`migration`, `batch`) VALUES
('2024_01_01_000001_create_clinics_table', 1),
('2024_01_01_000002_create_professionals_table', 1),
('2024_01_01_000003_create_patients_table', 1),
('2024_01_01_000004_create_anamneses_and_documents_tables', 1),
('2024_01_01_000005_create_appointments_table', 1),
('2024_01_01_000006_create_financial_tables', 1),
('2024_01_01_000007_create_whatsapp_logs_table', 1);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;