-- Mobilya ERP — MySQL 8+ / InnoDB
-- Charset: utf8mb4
-- İlk kurulum sonrası: admin@example.com / şifre: password  (hemen değiştirin)

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS mobilyaci;
CREATE DATABASE mobilyaci CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mobilyaci;

-- ---------------------------------------------------------------------------
-- ORG STRUCTURE
-- ---------------------------------------------------------------------------
CREATE TABLE branches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(32) NOT NULL,
    name VARCHAR(191) NOT NULL,
    phone VARCHAR(64) NULL,
    address TEXT 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,
    UNIQUE KEY uq_branches_code (code),
    KEY idx_branches_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(32) NOT NULL,
    name VARCHAR(191) NOT NULL,
    type ENUM('warehouse','showroom') NOT NULL DEFAULT 'warehouse',
    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,
    UNIQUE KEY uq_warehouses_branch_code (branch_id, code),
    KEY idx_warehouses_branch (branch_id),
    CONSTRAINT fk_warehouses_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- RBAC
-- ---------------------------------------------------------------------------
CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL,
    name VARCHAR(128) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_roles_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(96) NOT NULL,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_permissions_slug (slug)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    default_branch_id BIGINT UNSIGNED NULL,
    email VARCHAR(191) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(96) NOT NULL,
    last_name VARCHAR(96) NOT NULL,
    phone VARCHAR(64) NULL,
    max_discount_percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    max_installment_months TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_branch (default_branch_id),
    KEY idx_users_active_deleted (is_active, deleted_at),
    CONSTRAINT fk_users_default_branch FOREIGN KEY (default_branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

CREATE TABLE user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_branches (
    user_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, branch_id),
    CONSTRAINT fk_ub_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ub_branch FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- CRM / KVKK
-- ---------------------------------------------------------------------------
CREATE TABLE customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NULL,
    code VARCHAR(64) NULL,
    type ENUM('individual','company') NOT NULL DEFAULT 'individual',
    party_kind ENUM('musteri','cari') NOT NULL DEFAULT 'musteri' COMMENT 'musteri=tek seferlik; cari=tedarikçi/bayi',
    first_name VARCHAR(96) NULL,
    last_name VARCHAR(96) NULL,
    company_name VARCHAR(191) NULL,
    tax_id VARCHAR(32) NULL,
    email VARCHAR(191) NULL,
    phone VARCHAR(64) NOT NULL,
    secondary_phone VARCHAR(64) NULL,
    address TEXT NULL,
    city VARCHAR(96) NULL,
    district VARCHAR(96) NULL,
    kvkk_consent TINYINT(1) NOT NULL DEFAULT 0,
    kvkk_consent_at DATETIME NULL,
    marketing_opt_in TINYINT(1) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    risk_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    KEY idx_customers_phone (phone),
    KEY idx_customers_branch (branch_id),
    KEY idx_customers_party_branch (party_kind, branch_id),
    KEY idx_customers_created (created_at),
    CONSTRAINT fk_customers_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- PRODUCTS & PRICING
-- ---------------------------------------------------------------------------
CREATE TABLE products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(64) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    cost_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    cash_price DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    annual_interest_rate DECIMAL(7,4) NOT NULL DEFAULT 0.0000 COMMENT 'For auto installment total when plan price not fixed',
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    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,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_products_sku (sku),
    KEY idx_products_active (is_active, deleted_at),
    KEY idx_products_name (name)
) ENGINE=InnoDB;

CREATE TABLE product_payment_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    months TINYINT UNSIGNED NOT NULL COMMENT '1-12',
    total_price DECIMAL(14,4) NULL COMMENT 'NULL = derive from interest formula',
    manual_price TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_month (product_id, months),
    KEY idx_ppp_product (product_id),
    CONSTRAINT fk_ppp_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- WAREHOUSE STOCK LEVELS (materialized; movements are source of truth audit)
-- ---------------------------------------------------------------------------
CREATE TABLE warehouse_stocks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_available DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    qty_reserved DECIMAL(14,3) NOT NULL DEFAULT 0.000,
    qty_sold_reserved DECIMAL(14,3) NOT NULL DEFAULT 0.000 COMMENT 'Sold but physically in warehouse until delivery',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_wh_product (warehouse_id, product_id),
    KEY idx_ws_product (product_id),
    CONSTRAINT fk_ws_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_ws_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

CREATE TABLE stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_delta DECIMAL(14,3) NOT NULL,
    movement_type ENUM(
        'purchase','sale','adjustment','reservation_hold','reservation_release',
        'delivery_out','transfer_in','transfer_out','sold_reserve_in','sold_reserve_out'
    ) NOT NULL,
    reference_type VARCHAR(64) NULL,
    reference_id BIGINT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_sm_wh_product (warehouse_id, product_id),
    KEY idx_sm_ref (reference_type, reference_id),
    KEY idx_sm_created (created_at),
    CONSTRAINT fk_sm_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_sm_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_sm_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- PURCHASE INVOICES (alış / tedarikçi — stok girişi ile bağlantılı)
-- ---------------------------------------------------------------------------
CREATE TABLE purchase_invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    supplier_name VARCHAR(191) NOT NULL,
    supplier_tax_id VARCHAR(32) NULL,
    document_no VARCHAR(64) NOT NULL,
    document_date DATE NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    subtotal DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    tax_total DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    grand_total DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pi_branch_date (branch_id, document_date),
    KEY idx_pi_wh (warehouse_id),
    CONSTRAINT fk_pi_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_pi_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_pi_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE purchase_invoice_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_invoice_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    unit_cost_net DECIMAL(14,4) NOT NULL COMMENT 'KDV hariç birim maliyet',
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00,
    line_subtotal DECIMAL(14,4) NOT NULL,
    line_tax DECIMAL(14,4) NOT NULL,
    line_total DECIMAL(14,4) NOT NULL,
    KEY idx_pil_invoice (purchase_invoice_id),
    KEY idx_pil_product (product_id),
    CONSTRAINT fk_pil_invoice FOREIGN KEY (purchase_invoice_id) REFERENCES purchase_invoices(id) ON DELETE CASCADE,
    CONSTRAINT fk_pil_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- ORDERS (separate from invoice)
-- ---------------------------------------------------------------------------
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    sales_user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('LEAD','ORDER','RESERVATION','DELIVERY','INVOICE','PAYMENT','CLOSED','CANCELLED') NOT NULL DEFAULT 'LEAD',
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    subtotal DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    discount_total DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    tax_total DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    grand_total DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    is_official TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Official vs unofficial ledger classification',
    approved_discount_override TINYINT(1) NOT NULL DEFAULT 0,
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_orders_branch_status (branch_id, status),
    KEY idx_orders_customer (customer_id),
    KEY idx_orders_sales_user (sales_user_id),
    KEY idx_orders_created (created_at),
    CONSTRAINT fk_orders_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_orders_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_orders_sales_user FOREIGN KEY (sales_user_id) REFERENCES users(id),
    CONSTRAINT fk_orders_approved_by FOREIGN KEY (approved_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL DEFAULT 1.000,
    unit_list_price DECIMAL(14,4) NOT NULL COMMENT 'Snapshot at order time',
    unit_sale_price DECIMAL(14,4) NOT NULL,
    discount_percentage DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    line_subtotal DECIMAL(14,4) NOT NULL,
    line_tax DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    line_total DECIMAL(14,4) NOT NULL,
    installment_months TINYINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_oi_order (order_id),
    KEY idx_oi_product (product_id),
    CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- RESERVATIONS
-- ---------------------------------------------------------------------------
CREATE TABLE reservations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    delivery_date DATE NOT NULL,
    status ENUM('active','released','fulfilled','cancelled','reallocated') NOT NULL DEFAULT 'active',
    allow_reallocation TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_reservations_delivery (delivery_date),
    KEY idx_reservations_status (status),
    KEY idx_reservations_customer (customer_id),
    CONSTRAINT fk_res_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_res_oi FOREIGN KEY (order_item_id) REFERENCES order_items(id),
    CONSTRAINT fk_res_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_res_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- INVOICES (logical entity; e-invoice integration tracked separately)
-- ---------------------------------------------------------------------------
CREATE TABLE invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NOT NULL,
    invoice_number VARCHAR(64) NULL,
    issue_date DATE NOT NULL,
    total DECIMAL(14,4) NOT NULL,
    is_official TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_invoices_order (order_id),
    KEY idx_invoices_branch_date (branch_id, issue_date),
    UNIQUE KEY uq_invoices_branch_number (branch_id, invoice_number),
    CONSTRAINT fk_invoices_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_invoices_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- Per-branch per-month atomic invoice counters (LAST_INSERT_ID upsert pattern)
CREATE TABLE invoice_sequences (
    branch_id BIGINT UNSIGNED NOT NULL,
    period_key CHAR(6) NOT NULL COMMENT 'YYYYMM from issue_date',
    last_number INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (branch_id, period_key),
    CONSTRAINT fk_invseq_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- PAYMENTS & INSTALLMENTS
-- ---------------------------------------------------------------------------
CREATE TABLE installments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    sequence_no TINYINT UNSIGNED NOT NULL,
    due_date DATE NOT NULL,
    amount DECIMAL(14,4) NOT NULL,
    principal_amount DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    interest_amount DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
    status ENUM('pending','partial','paid','overdue','written_off') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_installment_seq (order_id, sequence_no),
    KEY idx_installments_due (due_date, status),
    CONSTRAINT fk_inst_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    installment_id BIGINT UNSIGNED NULL,
    method ENUM('cash','installment','cheque','promissory_note','bank_transfer','open_account') NOT NULL,
    amount DECIMAL(14,4) NOT NULL,
    paid_at DATETIME NULL,
    due_date DATE NULL COMMENT 'Cheque/note maturity',
    reference_no VARCHAR(128) NULL,
    bank_name VARCHAR(128) NULL,
    is_official TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payments_order (order_id),
    KEY idx_payments_method_date (method, paid_at),
    KEY idx_payments_due (due_date),
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_payments_inst FOREIGN KEY (installment_id) REFERENCES installments(id),
    CONSTRAINT fk_payments_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- SMS QUEUE / LOGS
-- ---------------------------------------------------------------------------
CREATE TABLE sms_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone_e164 VARCHAR(32) NOT NULL,
    message_body TEXT NOT NULL,
    template_key VARCHAR(64) NOT NULL,
    metadata_json JSON NULL,
    status ENUM('queued','sending','sent','failed') NOT NULL DEFAULT 'queued',
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    KEY idx_sms_queue_status (status, created_at)
) ENGINE=InnoDB;

CREATE TABLE sms_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_id BIGINT UNSIGNED NULL,
    phone_e164 VARCHAR(32) NOT NULL,
    template_key VARCHAR(64) NOT NULL,
    provider VARCHAR(64) NULL,
    response_json JSON NULL,
    status ENUM('sent','failed') NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_sms_logs_phone (phone_e164, created_at),
    CONSTRAINT fk_sms_logs_queue FOREIGN KEY (queue_id) REFERENCES sms_queue(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- E-INVOICE INTEGRATION
-- ---------------------------------------------------------------------------
CREATE TABLE invoice_integrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(64) NOT NULL,
    external_reference VARCHAR(128) NULL,
    request_payload JSON NULL,
    response_payload JSON NULL,
    status ENUM('pending','submitted','accepted','rejected','cancelled') NOT NULL DEFAULT 'pending',
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_invint_invoice (invoice_id),
    KEY idx_invint_status (status),
    CONSTRAINT fk_invint_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- AUDIT
-- ---------------------------------------------------------------------------
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(128) NOT NULL,
    entity_type VARCHAR(64) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    payload_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_user_time (user_id, created_at),
    KEY idx_audit_entity (entity_type, entity_id),
    KEY idx_audit_action_time (action, created_at),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------------
-- REPORTING INDEX HELPERS (denormalized rollups can be added later)
-- ---------------------------------------------------------------------------

-- ---------------------------------------------------------------------------
-- SEED DATA
-- ---------------------------------------------------------------------------
INSERT INTO branches (code, name, phone, is_active) VALUES
('MERKEZ', 'Merkez Şube', '+90-212-0000000', 1);

INSERT INTO warehouses (branch_id, code, name, type, is_active) VALUES
(1, 'DEPO-1', 'Ana Depo', 'warehouse', 1),
(1, 'SHOW-1', 'Vitrin Mağaza', 'showroom', 1);

INSERT INTO roles (slug, name) VALUES
('super_admin', 'Super Admin'),
('sales_manager', 'Satış Müdürü'),
('sales_staff', 'Satış Temsilcisi');

INSERT INTO permissions (slug, description) VALUES
('*', 'Wildcard — full access'),
('dashboard.view', 'Dashboard'),
('sales.view', 'Sales module'),
('sales.create', 'Create / edit sales documents'),
('crm.view', 'Customer CRM'),
('products.manage', 'Product catalog'),
('stock.view', 'Stock & warehouses'),
('reservations.manage', 'Reservations'),
('finance.view', 'Finance dashboard'),
('reports.view', 'Reporting module'),
('admin.users', 'User administration'),
('audit.view', 'Audit log read'),
('sales.approve_discount', 'İndirim limiti üstü onayı');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug = 'super_admin';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('dashboard.view','sales.view','sales.create','crm.view','reports.view','sales.approve_discount')
WHERE r.slug = 'sales_manager';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.slug IN ('dashboard.view','sales.view','crm.view','reports.view','stock.view')
WHERE r.slug = 'sales_staff';

-- Password: password  (bcrypt) — rotate in production
INSERT INTO users (default_branch_id, email, password_hash, first_name, last_name, max_discount_percentage, max_installment_months, is_active)
VALUES (
    1,
    'admin@example.com',
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'System',
    'Administrator',
    100.00,
    12,
    1
);

INSERT INTO user_roles (user_id, role_id)
SELECT u.id, r.id FROM users u JOIN roles r ON r.slug = 'super_admin' WHERE u.email = 'admin@example.com';

INSERT INTO user_branches (user_id, branch_id)
SELECT u.id, 1 FROM users u WHERE u.email = 'admin@example.com';

INSERT INTO customers (branch_id, type, first_name, last_name, phone, kvkk_consent, kvkk_consent_at)
VALUES (1, 'individual', 'Demo', 'Müşteri', '+905551112233', 1, NOW());

INSERT INTO products (sku, name, cost_price, cash_price, annual_interest_rate, vat_rate, is_active) VALUES
('KOLTUK-01', 'L koltuk', 20000.0000, 42000.0000, 24.0000, 20.00, 1),
('YEMEK-01', 'Yemek masası', 8000.0000, 18500.0000, 24.0000, 20.00, 1);

INSERT INTO warehouse_stocks (warehouse_id, product_id, qty_available, qty_reserved, qty_sold_reserved) VALUES
(1, 1, 10.000, 0.000, 0.000),
(1, 2, 25.000, 0.000, 0.000);

SET FOREIGN_KEY_CHECKS = 1;