-- V2.5 ilişkisel tablolar; mevcut app_state ve kullanıcı kayıtları korunur.
CREATE TABLE IF NOT EXISTS suppliers (
 id BIGINT UNSIGNED PRIMARY KEY, name VARCHAR(200) NOT NULL, email VARCHAR(190) NULL,
 phone VARCHAR(70) NULL, city VARCHAR(130) NULL, address TEXT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'TRY', discount_rate DECIMAL(7,4) NOT NULL DEFAULT 0,
 extra_json JSON NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY ix_supplier_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED PRIMARY KEY, supplier_id BIGINT UNSIGNED NULL, name VARCHAR(230) NOT NULL,
 sku VARCHAR(120) NOT NULL, unit VARCHAR(50) NOT NULL DEFAULT 'Adet',
 list_price DECIMAL(18,4) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'TRY',
 vat_rate DECIMAL(7,4) NOT NULL DEFAULT 0, discount_rate DECIMAL(7,4) NULL DEFAULT NULL, image_path TEXT NULL, extra_json JSON NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY ix_products_supplier(supplier_id), KEY ix_products_sku(sku),
 CONSTRAINT fk_product_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS purchase_orders (
 order_no VARCHAR(70) PRIMARY KEY, supplier_id BIGINT UNSIGNED NULL,
 order_date DATE NULL, status VARCHAR(40) NOT NULL DEFAULT 'draft', general_note TEXT NULL,
 sent_at DATETIME NULL, first_viewed_at DATETIME NULL, extra_json JSON NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY ix_order_supplier(supplier_id), KEY ix_order_status(status),
 CONSTRAINT fk_order_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS purchase_order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(70) NOT NULL,
 line_no INT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NULL,
 quantity DECIMAL(18,4) NOT NULL, list_price DECIMAL(18,4) NOT NULL,
 discount_rate DECIMAL(7,4) NOT NULL DEFAULT 0, net_unit_price DECIMAL(18,4) NOT NULL,
 vat_rate DECIMAL(7,4) NOT NULL DEFAULT 0, currency CHAR(3) NOT NULL DEFAULT 'TRY',
 requested_date DATE NULL, extra_json JSON NULL,
 UNIQUE KEY uk_order_line(order_no,line_no), KEY ix_line_product(product_id),
 CONSTRAINT fk_item_order FOREIGN KEY(order_no) REFERENCES purchase_orders(order_no) ON DELETE CASCADE,
 CONSTRAINT fk_item_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS supplier_invoices (
 invoice_no VARCHAR(120) PRIMARY KEY, supplier_id BIGINT UNSIGNED NULL,
 invoice_date DATE NULL, amount DECIMAL(18,4) NOT NULL DEFAULT 0,
 currency CHAR(3) NOT NULL DEFAULT 'TRY', status VARCHAR(40) NOT NULL DEFAULT 'pending',
 file_name VARCHAR(255) NULL, extra_json JSON NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY ix_invoice_supplier(supplier_id), CONSTRAINT fk_invoice_supplier FOREIGN KEY(supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;