-- ============================================================
-- İhracat Takip Sistemi — v1 Veritabanı Şeması
-- Encoding: UTF-8 | Engine: InnoDB
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. ÜLKELER
-- ------------------------------------------------------------
CREATE TABLE `countries` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(100) NOT NULL UNIQUE,
    `code`       VARCHAR(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. PARA BİRİMLERİ (CURRENCIES)
-- ------------------------------------------------------------
CREATE TABLE `currencies` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code`       VARCHAR(10) NOT NULL UNIQUE,          -- USD, EUR vb.
    `symbol`     VARCHAR(5) DEFAULT NULL,              -- $, € vb.
    `name`       VARCHAR(50) NOT NULL,                 -- US Dollar, Euro
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. FİRMALAR (COMPANIES)
-- ------------------------------------------------------------
CREATE TABLE `companies` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_name` VARCHAR(150) NOT NULL,
    `is_active`    TINYINT(1) NOT NULL DEFAULT 1,
    `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;

-- ------------------------------------------------------------
-- 4. KULLANICILAR
-- ------------------------------------------------------------
CREATE TABLE `users` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id` INT UNSIGNED NOT NULL,
    `name`       VARCHAR(100) NOT NULL,
    `email`      VARCHAR(150) NOT NULL UNIQUE,
    `password`   VARCHAR(255) NOT NULL,              -- bcrypt hash
    `role`       ENUM('admin','exporter','operation','finance') NOT NULL DEFAULT 'exporter',
    `is_active`  TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`),
    CONSTRAINT `fk_user_company` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. MÜŞTERİLER
-- ------------------------------------------------------------
CREATE TABLE `customers` (
    `id`                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_name`         VARCHAR(200) NOT NULL,
    `country_id`           INT UNSIGNED NOT NULL,
    `company_id`           INT UNSIGNED NOT NULL,
    `contact_person`       VARCHAR(150) DEFAULT NULL,
    `phone`                VARCHAR(50)  DEFAULT NULL,
    `email`                VARCHAR(150) DEFAULT NULL,
    `currency_id`          INT UNSIGNED NOT NULL,
    `payment_term_days`    SMALLINT UNSIGNED NOT NULL DEFAULT 30,  -- vade süresi (gün)
    `exporter_user_id`     INT UNSIGNED DEFAULT NULL,              -- sorumlu ihracatçı
    `operation_user_id`    INT UNSIGNED DEFAULT NULL,              -- sorumlu operasyoncu
    `notes`                TEXT DEFAULT NULL,
    `is_active`            TINYINT(1) NOT NULL DEFAULT 1,
    `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`),
    KEY `idx_country` (`country_id`),
    KEY `idx_currency` (`currency_id`),
    KEY `idx_exporter` (`exporter_user_id`),
    KEY `idx_operation` (`operation_user_id`),
    CONSTRAINT `fk_customer_comp`      FOREIGN KEY (`company_id`)        REFERENCES `companies`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_customer_country`   FOREIGN KEY (`country_id`)        REFERENCES `countries`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_customer_currency`  FOREIGN KEY (`currency_id`)       REFERENCES `currencies`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_customer_exporter`  FOREIGN KEY (`exporter_user_id`)  REFERENCES `users`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_customer_operation` FOREIGN KEY (`operation_user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. SATIŞLAR
-- ------------------------------------------------------------
CREATE TABLE `sales` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`    INT UNSIGNED NOT NULL,
    `customer_id`   INT UNSIGNED NOT NULL,
    `sale_date`     DATE NOT NULL,
    `country_id`    INT UNSIGNED NOT NULL,              -- müşteriden kopyalanır, override edilebilir
    `amount`        DECIMAL(15,2) NOT NULL,
    `currency_id`   INT UNSIGNED NOT NULL,
    `due_date`      DATE NOT NULL,                      -- satış_tarihi + vade_süresi
    `status`        ENUM('unpaid','paid','partial') NOT NULL DEFAULT 'unpaid',
    `notes`         TEXT DEFAULT NULL,
    `created_by`    INT UNSIGNED DEFAULT NULL,          -- kaydı giren kullanıcı
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company`   (`company_id`),
    KEY `idx_customer`  (`customer_id`),
    KEY `idx_sale_date` (`sale_date`),
    KEY `idx_due_date`  (`due_date`),
    KEY `idx_status`    (`status`),
    KEY `idx_country`   (`country_id`),
    KEY `idx_currency`  (`currency_id`),
    CONSTRAINT `fk_sale_comp`       FOREIGN KEY (`company_id`)  REFERENCES `companies`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sale_country`    FOREIGN KEY (`country_id`)  REFERENCES `countries`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_sale_currency`   FOREIGN KEY (`currency_id`) REFERENCES `currencies`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_sale_customer`   FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_sale_created_by` FOREIGN KEY (`created_by`)  REFERENCES `users`(`id`)     ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. SATIŞA EKLİ DOSYALAR
-- ------------------------------------------------------------
CREATE TABLE `sale_files` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`  INT UNSIGNED NOT NULL,
    `sale_id`     INT UNSIGNED NOT NULL,
    `file_name`   VARCHAR(255) NOT NULL,               -- orijinal dosya adı
    `file_path`   VARCHAR(500) NOT NULL,               -- sunucu yolu: uploads/sales/{sale_id}/...
    `file_type`   VARCHAR(50)  DEFAULT NULL,            -- mime type (application/pdf vb.)
    `file_size`   INT UNSIGNED DEFAULT NULL,            -- byte cinsinden
    `uploaded_by` INT UNSIGNED DEFAULT NULL,
    `uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`),
    KEY `idx_sale_id` (`sale_id`),
    CONSTRAINT `fk_file_comp`        FOREIGN KEY (`company_id`)  REFERENCES `companies`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_file_sale`        FOREIGN KEY (`sale_id`)     REFERENCES `sales`(`id`)  ON DELETE CASCADE,
    CONSTRAINT `fk_file_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. TAHSİLATLAR (v1: basit / v2'de genişletilecek)
-- ------------------------------------------------------------
CREATE TABLE `collections` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `company_id`       INT UNSIGNED NOT NULL,
    `sale_id`          INT UNSIGNED NOT NULL,
    `collected_amount` DECIMAL(15,2) NOT NULL,
    `collection_date`  DATE NOT NULL,
    `collected_by`     INT UNSIGNED DEFAULT NULL,
    `notes`            TEXT DEFAULT NULL,
    `created_at`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_company` (`company_id`),
    KEY `idx_sale_id` (`sale_id`),
    CONSTRAINT `fk_collection_comp`         FOREIGN KEY (`company_id`)   REFERENCES `companies`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_collection_sale`         FOREIGN KEY (`sale_id`)      REFERENCES `sales`(`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_collection_collected_by` FOREIGN KEY (`collected_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. SİSTEM LOG (isteğe bağlı — audit trail)
-- ------------------------------------------------------------
CREATE TABLE `activity_logs` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`    INT UNSIGNED DEFAULT NULL,
    `action`     VARCHAR(100) NOT NULL,                -- 'create_sale', 'update_customer' vb.
    `target`     VARCHAR(100) DEFAULT NULL,            -- 'sales', 'customers' vb.
    `target_id`  INT UNSIGNED DEFAULT NULL,
    `payload`    JSON DEFAULT NULL,                    -- değişen veriler
    `ip`         VARCHAR(45) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_action`  (`action`),
    CONSTRAINT `fk_log_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- 9. SERVİS LOGLARI (API İstekleri)
-- ------------------------------------------------------------
CREATE TABLE `ServiceLog` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_data`  LONGTEXT DEFAULT NULL,
    `response_data` LONGTEXT DEFAULT NULL,
    `service`       VARCHAR(100) DEFAULT NULL,
    `request_time`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `response_time` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- YARDIMCI VIEW'LAR
-- ============================================================

-- Vadesi geçmiş satışlar + gecikme günü + segment
CREATE OR REPLACE VIEW `v_overdue_sales` AS
SELECT
    s.company_id,
    s.id                                        AS sale_id,
    c.company_name,
    cn.name                                     AS country,
    s.sale_date,
    s.due_date,
    s.amount,
    cur.code                                    AS currency,
    DATEDIFF(CURDATE(), s.due_date)             AS overdue_days,
    CASE
        WHEN DATEDIFF(CURDATE(), s.due_date) >= 90 THEN '90+'
        WHEN DATEDIFF(CURDATE(), s.due_date) >= 60 THEN '60+'
        WHEN DATEDIFF(CURDATE(), s.due_date) >= 30 THEN '30+'
        ELSE 'current'
    END                                         AS overdue_segment,
    u_exp.name                                  AS exporter_name,
    u_ops.name                                  AS operation_name
FROM sales s
JOIN customers c   ON s.customer_id  = c.id
JOIN countries cn  ON c.country_id   = cn.id
JOIN currencies cur ON s.currency_id = cur.id
LEFT JOIN users u_exp ON c.exporter_user_id  = u_exp.id
LEFT JOIN users u_ops ON c.operation_user_id = u_ops.id
WHERE s.status IN ('unpaid', 'partial')
  AND s.due_date < CURDATE();

-- Ülke bazlı açık bakiye özeti
CREATE OR REPLACE VIEW `v_country_open_balance` AS
SELECT
    s.company_id,
    cn.name           AS country,
    cur.code          AS currency,
    COUNT(s.id)       AS sale_count,
    SUM(s.amount)     AS total_amount
FROM sales s
JOIN countries cn ON s.country_id = cn.id
JOIN currencies cur ON s.currency_id = cur.id
WHERE s.status IN ('unpaid', 'partial')
GROUP BY s.company_id, cn.name, cur.code;

-- Son 6 ay aylık satış trendi
CREATE OR REPLACE VIEW `v_monthly_sales_trend` AS
SELECT
    s.company_id,
    DATE_FORMAT(s.sale_date, '%Y-%m') AS month,
    cur.code                          AS currency,
    COUNT(s.id)                       AS sale_count,
    SUM(s.amount)                     AS total_amount
FROM sales s
JOIN currencies cur ON s.currency_id = cur.id
WHERE s.sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY s.company_id, DATE_FORMAT(s.sale_date, '%Y-%m'), cur.code
ORDER BY month ASC;

-- Satış Bazlı Bakiye (Detaylı)
CREATE OR REPLACE VIEW `v_sales_balance` AS
SELECT
    s.company_id,
    s.id AS sale_id,
    s.customer_id,
    s.amount AS total_amount,
    cur.code AS currency,
    IFNULL(SUM(c.collected_amount), 0) AS total_collected,
    (s.amount - IFNULL(SUM(c.collected_amount), 0)) AS open_balance
FROM sales s
JOIN currencies cur ON s.currency_id = cur.id
LEFT JOIN collections c ON s.id = c.sale_id
GROUP BY s.company_id, s.id, cur.code;

-- Ülke Bazlı Müşteri Dağılımı (Dashboard için)
CREATE OR REPLACE VIEW `v_customer_distribution` AS
SELECT
    c.company_id,
    cn.name AS country,
    COUNT(c.id) AS customer_count
FROM customers c
JOIN countries cn ON c.country_id = cn.id
WHERE c.is_active = 1
GROUP BY c.company_id, cn.name;

-- Güncel Toplam Satışlar (Dashboard için)
CREATE OR REPLACE VIEW `v_total_sales_summary` AS
SELECT
    s.company_id,
    cur.code AS currency,
    SUM(s.amount) AS total_sales_amount
FROM sales s
JOIN currencies cur ON s.currency_id = cur.id
GROUP BY s.company_id, cur.code;

-- ============================================================
-- ÖRNEK VERİ (opsiyonel — test için)
-- ============================================================

INSERT INTO `countries` (`id`, `name`, `code`) VALUES
(1, 'Germany', 'DE'),
(2, 'USA', 'US'),
(3, 'Italy', 'IT'),
(4, 'France', 'FR'),
(5, 'United Kingdom', 'UK');

INSERT INTO `currencies` (`id`, `code`, `symbol`, `name`) VALUES
(1, 'USD', '$', 'US Dollar'),
(2, 'EUR', '€', 'Euro'),
(3, 'GBP', '£', 'British Pound'),
(4, 'TRY', '₺', 'Turkish Lira');

-- Şirket ekleyelim (Örnek)
INSERT INTO `companies` (`id`, `company_name`) VALUES 
(1, 'Tech Solutions A.Ş.'),
(2, 'Global Export Inc.');

INSERT INTO `users` (`company_id`, `name`, `email`, `password`, `role`) VALUES
(1, 'Admin Tech',     'admin@tech.com',     '$2y$10$89.r81.TfU8cM/6vR.kX3.f5u9A1z8c2.kFv5z2v.kFv5z2v.kFv', 'admin'), -- hash for 123456 (approx)
(1, 'Ali İhracatçı',  'ali@tech.com',       '$2y$10$89.r81.TfU8cM/6vR.kX3.f5u9A1z8c2.kFv5z2v.kFv5z2v.kFv', 'exporter'),
(1, 'Mehmet Ops',     'mehmet@tech.com',    '$2y$10$89.r81.TfU8cM/6vR.kX3.f5u9A1z8c2.kFv5z2v.kFv5z2v.kFv', 'operation'),
(1, 'Ayşe Finans',    'ayse@tech.com',      '$2y$10$89.r81.TfU8cM/6vR.kX3.f5u9A1z8c2.kFv5z2v.kFv5z2v.kFv', 'finance'),
(2, 'Admin Global',   'admin@global.com',   '$2y$10$89.r81.TfU8cM/6vR.kX3.f5u9A1z8c2.kFv5z2v.kFv5z2v.kFv', 'admin');

INSERT INTO `customers` (`company_id`, `company_name`, `country_id`, `contact_person`, `email`, `currency_id`, `payment_term_days`, `exporter_user_id`, `operation_user_id`) VALUES
(1, 'Acme GmbH',        1, 'Hans Müller',   'hans@acme.de',      2, 60, 2, 3),
(1, 'TradeLink LLC',    2, 'John Smith',    'john@tradelink.com',1, 30, 2, 3),
(2, 'Alfa Italia SRL',  3, 'Marco Rossi',   'marco@alfa.it',     2, 90, NULL, NULL);