-- =====================================================================
--  ArNi Solutions CRM – Datenbankschema
--  PHP 7.3 / MySQL / MariaDB · utf8mb4 · InnoDB
--  Hinweis: Die Datenbank wird mit anderen Anwendungen geteilt, daher
--           sind ALLE Tabellen mit dem Prefix `crm_` versehen.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Benutzer / Rollen
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_users` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`          VARCHAR(120) NOT NULL,
  `email`         VARCHAR(190) NOT NULL,
  `password`      VARCHAR(255) NOT NULL,
  `role`          ENUM('admin','office','support') NOT NULL DEFAULT 'office',
  `active`        TINYINT(1) NOT NULL DEFAULT 1,
  `last_login_at` DATETIME NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Einstellungen (Key/Value): Firmendaten, SMTP, Nummernkreise, Texte ...
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_settings` (
  `skey`   VARCHAR(80) NOT NULL,
  `svalue` MEDIUMTEXT NULL,
  PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Kunden (OPTIONAL – Rechnungen gehen auch ohne Kunde)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_customers` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company`          VARCHAR(190) NULL,
  `contact_name`     VARCHAR(190) NULL,
  `address1`         VARCHAR(190) NULL,
  `zip`              VARCHAR(20)  NULL,
  `city`             VARCHAR(120) NULL,
  `country`          VARCHAR(80)  NOT NULL DEFAULT 'Deutschland',
  `email`            VARCHAR(190) NULL,
  `phone`            VARCHAR(60)  NULL,
  `vat_id`           VARCHAR(40)  NULL,
  `notes`            TEXT NULL,
  `mail_template_id` INT UNSIGNED NULL,    -- spezielles Mail-Template für diesen Kunden
  `active`           TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cust_company` (`company`),
  KEY `idx_cust_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Produkte / Leistungen
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_products` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`       VARCHAR(190) NOT NULL,
  `description` TEXT NULL,
  `price_net`   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_rate`    DECIMAL(5,2) NOT NULL DEFAULT 19.00,
  `unit`        VARCHAR(40) NOT NULL DEFAULT 'Stk.',
  `category`    VARCHAR(120) NULL,
  `active`      TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_prod_active` (`active`),
  KEY `idx_prod_cat` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Rechnungen (Rechnungsadresse wird IMMER als Snapshot gespeichert,
-- egal ob Kunde gewählt oder frei eingegeben wurde)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_invoices` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_number`   VARCHAR(60) NULL,           -- erst bei Status != Entwurf vergeben
  `customer_id`      INT UNSIGNED NULL,          -- optional
  `customer_suggest_dismissed` TINYINT(1) NOT NULL DEFAULT 0, -- „Kunde anlegen?"-Vorschlag abgelehnt
  `bill_company`     VARCHAR(190) NULL,
  `bill_name`        VARCHAR(190) NULL,
  `bill_address1`    VARCHAR(190) NULL,
  `bill_zip`         VARCHAR(20)  NULL,
  `bill_city`        VARCHAR(120) NULL,
  `bill_country`     VARCHAR(80)  NULL,
  `bill_email`       VARCHAR(190) NULL,
  `bill_vat_id`      VARCHAR(40)  NULL,
  `invoice_date`     DATE NOT NULL,
  `service_date`     DATE NULL,
  `due_date`         DATE NULL,
  `payment_term_days` INT NOT NULL DEFAULT 14,
  `status`           ENUM('draft','open','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `text_top`         TEXT NULL,
  `text_bottom`      TEXT NULL,
  `subtotal_net`     DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `tax_total`        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_gross`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `pdf_path`         VARCHAR(255) NULL,
  `recurring_id`     INT UNSIGNED NULL,          -- aus wiederkehrender Rechnung erzeugt
  `notes`            TEXT NULL,
  `created_by`       INT UNSIGNED NULL,
  `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inv_number` (`invoice_number`),
  KEY `idx_inv_status` (`status`),
  KEY `idx_inv_customer` (`customer_id`),
  KEY `idx_inv_date` (`invoice_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Rechnungspositionen
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_invoice_items` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id`       INT UNSIGNED NOT NULL,
  `product_id`       INT UNSIGNED NULL,
  `position`         INT NOT NULL DEFAULT 0,     -- Sortierung (Drag&Drop)
  `title`            VARCHAR(190) NOT NULL,
  `description`      TEXT NULL,
  `qty`              DECIMAL(12,3) NOT NULL DEFAULT 1.000,
  `unit`             VARCHAR(40) NOT NULL DEFAULT 'Stk.',
  `unit_price`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `discount_percent` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `tax_rate`         DECIMAL(5,2) NOT NULL DEFAULT 19.00,
  `line_net`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_tax`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `line_gross`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_item_invoice` (`invoice_id`),
  CONSTRAINT `fk_item_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Zahlungen
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_invoice_payments` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id` INT UNSIGNED NOT NULL,
  `amount`     DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `paid_on`    DATE NOT NULL,
  `method`     VARCHAR(60) NULL,
  `note`       VARCHAR(190) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pay_invoice` (`invoice_id`),
  CONSTRAINT `fk_pay_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `crm_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Mail-Templates + Regeln + Log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_mail_templates` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(120) NOT NULL,
  `subject`    VARCHAR(255) NOT NULL,
  `body_html`  MEDIUMTEXT NOT NULL,
  `body_text`  MEDIUMTEXT NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `status_key` VARCHAR(40) NULL,          -- optional: Template für bestimmten Rechnungsstatus
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_tpl_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_mail_template_rules` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `scope`       ENUM('customer','status') NOT NULL,
  `match_value` VARCHAR(120) NOT NULL,    -- customer_id ODER status-key
  `template_id` INT UNSIGNED NOT NULL,
  `priority`    INT NOT NULL DEFAULT 10,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rule_scope` (`scope`,`match_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_mail_logs` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `invoice_id`  INT UNSIGNED NULL,
  `ticket_id`   INT UNSIGNED NULL,
  `template_id` INT UNSIGNED NULL,
  `to_email`    VARCHAR(190) NOT NULL,
  `subject`     VARCHAR(255) NULL,
  `status`      ENUM('sent','failed') NOT NULL,
  `error`       TEXT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_log_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Tickets
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_tickets` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_number`   VARCHAR(40) NULL,
  `customer_id`     INT UNSIGNED NULL,
  `invoice_id`      INT UNSIGNED NULL,
  `subject`         VARCHAR(190) NOT NULL,
  `description`     TEXT NULL,
  `status`          ENUM('open','in_progress','waiting_customer','done','closed') NOT NULL DEFAULT 'open',
  `priority`        ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `assigned_to`     INT UNSIGNED NULL,
  `requester_name`  VARCHAR(190) NULL,
  `requester_email` VARCHAR(190) NULL,
  `created_by`      INT UNSIGNED NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ticket_number` (`ticket_number`),
  KEY `idx_ticket_status` (`status`),
  KEY `idx_ticket_prio` (`priority`),
  KEY `idx_ticket_assigned` (`assigned_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_ticket_messages` (
  `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id`      INT UNSIGNED NOT NULL,
  `author_user_id` INT UNSIGNED NULL,
  `author_name`    VARCHAR(190) NULL,
  `is_public`      TINYINT(1) NOT NULL DEFAULT 1,   -- öffentliche Antwort vs. intern
  `body`           TEXT NOT NULL,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_ticket` (`ticket_id`),
  CONSTRAINT `fk_msg_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `crm_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `crm_ticket_notes` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id`  INT UNSIGNED NOT NULL,
  `user_id`    INT UNSIGNED NULL,
  `body`       TEXT NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_note_ticket` (`ticket_id`),
  CONSTRAINT `fk_note_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `crm_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Wiederkehrende Rechnungen (Blueprint als JSON)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_recurring_invoices` (
  `id`                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`             VARCHAR(190) NOT NULL,
  `source_invoice_id` INT UNSIGNED NULL,
  `customer_id`       INT UNSIGNED NULL,
  `interval_type`     ENUM('monthly','quarterly','semiannual','annual') NOT NULL DEFAULT 'monthly',
  `start_date`        DATE NOT NULL,
  `next_run`          DATE NOT NULL,
  `end_date`          DATE NULL,
  `auto_send`         TINYINT(1) NOT NULL DEFAULT 0,
  `active`            TINYINT(1) NOT NULL DEFAULT 1,
  `last_run`          DATE NULL,
  `blueprint`         LONGTEXT NOT NULL,    -- JSON: Rechnungskopf + Positionen
  `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`        DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_rec_active` (`active`),
  KEY `idx_rec_next` (`next_run`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Cron-Log
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_cron_logs` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job`        VARCHAR(80) NOT NULL,
  `message`    TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cron_job` (`job`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Dateien / Anhänge (optional, vorbereitet)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_files` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id`     INT UNSIGNED NULL,
  `invoice_id`    INT UNSIGNED NULL,
  `original_name` VARCHAR(255) NOT NULL,
  `stored_name`   VARCHAR(255) NOT NULL,
  `mime`          VARCHAR(120) NULL,
  `size`          INT UNSIGNED NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_file_ticket` (`ticket_id`),
  KEY `idx_file_invoice` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;