-- V3.3: PDF/JPG/PNG fatura arşivi ve manuel/OCR kontrollü satır mutabakatı.
CREATE TABLE IF NOT EXISTS invoice_documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 invoice_no VARCHAR(120) NOT NULL,
 supplier_id BIGINT UNSIGNED NOT NULL,
 order_no VARCHAR(70) NOT NULL,
 invoice_date DATE NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'TRY',
 original_name VARCHAR(255) NOT NULL,
 storage_name VARCHAR(100) NOT NULL,
 mime_type VARCHAR(60) NOT NULL,
 sha256 CHAR(64) NOT NULL,
 ocr_text MEDIUMTEXT NULL,
 ocr_status VARCHAR(30) NOT NULL DEFAULT 'not_configured',
 status VARCHAR(30) NOT NULL DEFAULT 'pending',
 created_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_supplier (supplier_id,invoice_no),
 UNIQUE KEY uq_invoice_hash (supplier_id,sha256),
 KEY ix_invoice_order (order_no),
 CONSTRAINT fk_doc_order FOREIGN KEY (order_no) REFERENCES purchase_orders(order_no) ON DELETE RESTRICT,
 CONSTRAINT fk_doc_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS invoice_document_lines (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 document_id BIGINT UNSIGNED NOT NULL,
 order_item_id BIGINT UNSIGNED NOT NULL,
 invoice_qty DECIMAL(18,4) NOT NULL,
 invoice_unit_price DECIMAL(18,4) NOT NULL,
 invoice_vat DECIMAL(7,4) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_invoice_line (document_id,order_item_id),
 CONSTRAINT fk_invoice_doc FOREIGN KEY(document_id) REFERENCES invoice_documents(id) ON DELETE CASCADE,
 CONSTRAINT fk_invoice_order_item FOREIGN KEY(order_item_id) REFERENCES purchase_order_items(id) ON DELETE RESTRICT,
 CONSTRAINT chk_invoice_qty CHECK(invoice_qty>0),
 CONSTRAINT chk_invoice_price CHECK(invoice_unit_price>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;