-- Ön sipariş sistemi için örnek MySQL şeması

CREATE TABLE IF NOT EXISTS `orders` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company`       VARCHAR(255) NOT NULL DEFAULT '',
  `contact_name`  VARCHAR(255) NOT NULL DEFAULT '',
  `phone`         VARCHAR(100) NOT NULL DEFAULT '',
  `email`         VARCHAR(255) NOT NULL DEFAULT '',
  `note`          TEXT NULL,
  `discount_rate` DECIMAL(6,2) NOT NULL DEFAULT 0.00,          -- % olarak (0-100)
  `subtotal_excl` DECIMAL(18,2) NOT NULL DEFAULT 0.00,         -- KDV hariç ara toplam
  `vat_rate`      DECIMAL(6,2) NOT NULL DEFAULT 20.00,         -- % olarak
  `vat_amount`    DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  `total_incl`    DECIMAL(18,2) NOT NULL DEFAULT 0.00,         -- KDV + iskonto sonrası genel toplam
  `total_qty`     INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at`    DATETIME NOT NULL,
  `created_ip`    VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id`         INT UNSIGNED NOT NULL,
  `product_code`     VARCHAR(100) NOT NULL DEFAULT '',
  `product_name`     VARCHAR(500) NOT NULL DEFAULT '',
  `unit`             VARCHAR(50)  NOT NULL DEFAULT '',
  `unit_price_excl`  DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `qty`              INT UNSIGNED NOT NULL DEFAULT 0,
  `line_total_excl`  DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  CONSTRAINT `fk_order_items_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `app_users` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`      VARCHAR(50)  NOT NULL,
  `password`      VARCHAR(255) NOT NULL,
  `role`          ENUM('admin','customer') NOT NULL DEFAULT 'customer',
  `full_name`     VARCHAR(100) DEFAULT NULL,
  `company_name`  VARCHAR(255) DEFAULT NULL,
  `is_active`     TINYINT(1)   NOT NULL DEFAULT 1,
  `created_at`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`        VARCHAR(100) NOT NULL,
  `name`        VARCHAR(500) NOT NULL,
  `unit`        VARCHAR(50)  DEFAULT NULL,
  `price_excl`  DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
  `cat`         VARCHAR(255)  DEFAULT NULL,
  `is_active`   TINYINT(1)    NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `smtp_settings` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `host`        VARCHAR(255) NOT NULL,
  `port`        INT          NOT NULL DEFAULT 587,
  `secure`      ENUM('','ssl','tls') NOT NULL DEFAULT 'tls',
  `username`    VARCHAR(255) NOT NULL,
  `password`    VARCHAR(255) NOT NULL,
  `from_email`  VARCHAR(255) NOT NULL,
  `from_name`   VARCHAR(255) NOT NULL,
  `to_emails`   TEXT NOT NULL,
  `cc_emails`   TEXT DEFAULT NULL,
  `bcc_emails`  TEXT DEFAULT NULL,
  `debug`       TINYINT(1) NOT NULL DEFAULT 0,
  `vat_rate`    DECIMAL(6,2) NOT NULL DEFAULT 20.00,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;