-- ============================================================
-- SaaS Boilerplate — base schema
-- Multitenant, Super Admin, Deploy/Backup tools, Email/SMTP settings
-- ============================================================

CREATE TABLE IF NOT EXISTS `tenants` (
  `id`                    int(11)      NOT NULL AUTO_INCREMENT,
  `name`                  varchar(150) NOT NULL,
  `slug`                  varchar(100) NOT NULL,
  `is_active`             tinyint(1)   NOT NULL DEFAULT 1,
  `default_contact_name`  varchar(150) DEFAULT NULL,
  `default_contact_phone` varchar(20)  DEFAULT NULL,
  `created_at`            datetime     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`            datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-tenant optional module toggles — add a column per module as your
-- app grows (mirrors the ERP's rental_wo_enabled / po_enabled pattern).
-- Kept as a separate table (not columns on tenants) so adding new modules
-- never requires an ALTER TABLE on the core tenants table.
CREATE TABLE IF NOT EXISTS `tenant_modules` (
  `id`          int(11)      NOT NULL AUTO_INCREMENT,
  `tenant_id`   int(11)      NOT NULL,
  `module_key`  varchar(60)  NOT NULL COMMENT 'e.g. campaigns, templates, analytics',
  `is_enabled`  tinyint(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tenant_module` (`tenant_id`,`module_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `tenant_id`    int(11)      DEFAULT NULL COMMENT 'NULL for superadmin users (not tied to one tenant)',
  `name`         varchar(150) NOT NULL,
  `email`        varchar(150) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role`         enum('superadmin','admin','user') NOT NULL DEFAULT 'user',
  `is_active`    tinyint(1)   NOT NULL DEFAULT 1,
  `created_at`   datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Email/SMTP settings — same Gmail-primary + Brevo-fallback pattern proven
-- in the ERP (handles hosts that intercept outbound SMTP).
CREATE TABLE IF NOT EXISTS `email_settings` (
  `id`                  int(11)      NOT NULL AUTO_INCREMENT,
  `tenant_id`           int(11)      DEFAULT NULL COMMENT 'NULL = system-wide default (used by superadmin notifications)',
  `provider`            enum('smtp','gmail','brevo_api') NOT NULL DEFAULT 'smtp',
  `smtp_host`           varchar(150) DEFAULT NULL,
  `smtp_port`           int(11)      DEFAULT 587,
  `smtp_username`       varchar(150) DEFAULT NULL,
  `smtp_password`       varchar(255) DEFAULT NULL,
  `smtp_encryption`     enum('tls','ssl','none') DEFAULT 'tls',
  `from_name`           varchar(150) DEFAULT NULL,
  `from_email`          varchar(150) DEFAULT NULL,
  `fallback_api_key`    varchar(255) DEFAULT NULL COMMENT 'Brevo API key, used automatically if primary send fails',
  `created_at`          datetime     DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          datetime     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `email_log` (
  `id`            int(11)      NOT NULL AUTO_INCREMENT,
  `tenant_id`     int(11)      DEFAULT NULL,
  `to_email`      varchar(150) DEFAULT NULL,
  `subject`       varchar(255) DEFAULT NULL,
  `status`        enum('sent','failed') NOT NULL DEFAULT 'sent',
  `error`         text         DEFAULT NULL,
  `related_type`  varchar(50)  DEFAULT NULL,
  `related_id`    int(11)      DEFAULT NULL,
  `sent_at`       datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Self-service deploy tooling logs
CREATE TABLE IF NOT EXISTS `deploy_log` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `deployed_by`  int(11)      DEFAULT NULL,
  `file_name`    varchar(255) DEFAULT NULL,
  `files_list`   text         DEFAULT NULL,
  `file_count`   int(11)      NOT NULL DEFAULT 0,
  `created_at`   datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sql_run_log` (
  `id`               int(11)      NOT NULL AUTO_INCREMENT,
  `run_by`           int(11)      DEFAULT NULL,
  `sql_text`         longtext     DEFAULT NULL,
  `statement_count`  int(11)      NOT NULL DEFAULT 0,
  `success_count`    int(11)      NOT NULL DEFAULT 0,
  `created_at`       datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `db_backup_log` (
  `id`           int(11)      NOT NULL AUTO_INCREMENT,
  `run_by`       int(11)      DEFAULT NULL,
  `file_name`    varchar(255) DEFAULT NULL,
  `table_count`  int(11)      NOT NULL DEFAULT 0,
  `created_at`   datetime     DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NOTE: No seed Super Admin user is inserted here. Run setup.php once after
-- deploying (see README) to create your first Super Admin login — this
-- guarantees a correctly-formatted password hash regardless of which PHP
-- version generates it, rather than relying on a hardcoded hash that may
-- not match this server's bcrypt cost/format.