-- GOLDER VERİTABANI ŞEMASI (AI OPTİMİZE)
-- ÖNEMLİ KURAL: Tablolarda 'is_deleted' kolonu varsa, SADECE is_deleted = 0 olan (silinmemiş) kayıtları baz al!

CREATE TABLE app_settings (profile_id char(36), setting_key varchar(100), setting_value longtext, setting_type varchar(20));
CREATE TABLE audit_logs (id char(36), profile_id char(36), user_name varchar(100), action varchar(50), target_table varchar(50), target_id varchar(100), details text, ip_address varchar(50), created_at datetime);

CREATE TABLE barcode_history (id char(36), profile_id char(36), barcode varchar(100), product_type varchar(100), gram decimal(19,4), karat varchar(50), buy_date datetime, buy_from varchar(150), buy_price decimal(19,4), buy_method varchar(50), is_sold tinyint(1), sell_date datetime, sell_to varchar(150), sell_price decimal(19,4), sell_method varchar(50));
CREATE TABLE categories (id char(36), profile_id char(36), name varchar(100));

CREATE TABLE customer_history_events (id char(36), profile_id char(36), person_id char(36), person_full_name varchar(150), event_date datetime, event_type varchar(100), description text, debt_original decimal(19,4), credit_original decimal(19,4), currency varchar(20), transaction_amount decimal(19,4), related_id char(36), created_at datetime);

-- record_type: 'Receivable' (Alacak), 'Payable' (Borç) | debt_category: 'Cash', 'Product'
CREATE TABLE debt_records (id char(36), profile_id char(36), person_id char(36), person_full_name varchar(150), record_type varchar(20), transaction_date date, debt_category varchar(20), original_amount decimal(19,4), currency varchar(10), total_amount_base decimal(19,4), product_name varchar(150), product_quantity decimal(19,4), description text, status varchar(20), due_date date, is_paid tinyint(1), paid_amount_base decimal(19,4), paid_quantity decimal(19,4), payment_date date, is_deleted tinyint(1));

CREATE TABLE depots (id char(36), profile_id char(36), name varchar(100), is_deleted tinyint(1));
CREATE TABLE financial_accounts (id char(36), profile_id char(36), name varchar(100), type varchar(20), is_deleted tinyint(1));

-- transaction_type: 'Income' (Gelir/Giriş), 'Expense' (Gider/Çıkış)
CREATE TABLE financial_transactions (id char(36), profile_id char(36), account_id char(36), transaction_date date, transaction_type varchar(20), amount decimal(19,4), currency varchar(10), base_amount decimal(19,4), description text, related_person_id char(36), related_record_id char(36), is_deleted tinyint(1));

CREATE TABLE persons (id char(36), profile_id char(36), full_name varchar(150), phone_number varchar(50), address text, notes text, is_deleted tinyint(1));

-- stock_type: 'Product', 'Ziynet', 'Scrap'
CREATE TABLE product_definitions (id char(36), profile_id char(36), stock_type varchar(20), name varchar(150), category_id char(36), barcode_header varchar(50), karat_purity varchar(50), workmanship_fee decimal(19,4), workmanship_currency varchar(10), default_purchase_price decimal(19,4), default_sale_price decimal(19,4), purchase_currency varchar(10), sale_currency varchar(10), default_weight_gr decimal(19,4), is_active tinyint(1), is_deleted tinyint(1));

CREATE TABLE profiles (id char(36), name varchar(100), is_active tinyint(1), is_deleted tinyint(1));

-- status: 'Alındı', 'İşlemde', 'Hazır', 'Teslim Edildi'
CREATE TABLE repair_records (id char(36), profile_id char(36), customer_full_name varchar(150), product_description text, complaint_request text, receipt_date date, estimated_fee decimal(19,4), fee_currency varchar(10), status varchar(50), delivery_date date, notes text, is_deleted tinyint(1));

CREATE TABLE sales (id char(36), profile_id char(36), sale_date date, customer_full_name varchar(150), total_amount decimal(19,4), currency varchar(10), payment_method_summary text, products_json longtext, is_deleted tinyint(1));

CREATE TABLE scheduled_transactions (id char(36), profile_id char(36), due_date date, transaction_type varchar(20), amount decimal(19,4), currency varchar(10), description text, target varchar(100), person_full_name varchar(150), status varchar(20), is_deleted tinyint(1));

CREATE TABLE service_definitions (id char(36), profile_id char(36), name varchar(100), custom_columns_json longtext);
CREATE TABLE service_jobs (id char(36), profile_id char(36), service_def_id char(36), customer_name varchar(150), description text, status varchar(50), fee decimal(19,4), currency varchar(10), start_date date, delivery_date date, dynamic_data_json longtext);

-- stock_type: 'Product', 'Ziynet', 'Scrap'
CREATE TABLE stock_items (id char(36), profile_id char(36), depot_id char(36), stock_type varchar(20), item_type_name varchar(150), quantity decimal(19,4), quantity_gr decimal(19,4), purchase_price_base decimal(19,4), unique_barcode varchar(100), is_deleted tinyint(1));

CREATE TABLE stock_movements (id char(36), profile_id char(36), date datetime, item_type varchar(50), product_name varchar(150), operation_type varchar(50), quantity decimal(19,4), source varchar(100), destination varchar(100), description text, user varchar(100));

CREATE TABLE taxes (id char(36), profile_id char(36), name varchar(120), rate decimal(19,4), type varchar(10), country varchar(2), active tinyint(1));
CREATE TABLE tax_applications (id char(36), source_table varchar(60), source_id char(36), profile_id char(36), subtotal decimal(19,4), tax_total decimal(19,4), grand_total decimal(19,4), currency varchar(10), snapshot_json longtext, created_at datetime);

CREATE TABLE users (id char(36), profile_id char(36), username varchar(50), full_name varchar(100), role varchar(20), is_active tinyint(1), is_deleted tinyint(1));

