-- ==============================================
-- 1. Diller Tablosu
-- ==============================================
CREATE TABLE languages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL, -- tr, en, de vb.
    name VARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 2. Ölçü Birimleri Ana Tablosu
-- ==============================================
CREATE TABLE units (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL, -- KG, LT, PCS vb.
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 3. Ölçü Birimi Çevirileri Tablosu (unit_translations)
-- ==============================================
CREATE TABLE unit_translations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    unit_id INT NOT NULL,
    language_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,         -- Tam isim (ör. kilogram)
    abbreviation VARCHAR(20) NOT NULL, -- Kısaltma (ör. kg)
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 5. Aile Üyeleri / Alt Kullanıcılar Tablosu
-- ==============================================
CREATE TABLE family_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,          -- Ana kullanıcı
    member_user_id INT NOT NULL,   -- Alt kullanıcı
    role ENUM('primary','sub') NOT NULL DEFAULT 'sub',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (member_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 6. Depolar Tablosu
-- ==============================================
CREATE TABLE storages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 7. Ürünler Tablosu
-- ==============================================
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    storage_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_id INT NOT NULL,
    expiration_date DATE DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (storage_id) REFERENCES storages(id) ON DELETE CASCADE,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 8. Alışveriş Listeleri Tablosu
-- ==============================================
CREATE TABLE shopping_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 9. Alışveriş Listesi Ürünleri Tablosu
-- ==============================================
CREATE TABLE shopping_list_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shopping_list_id INT NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_id INT NOT NULL,
    added_to_storage TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shopping_list_id) REFERENCES shopping_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ==============================================
-- 10. Abonelikler Tablosu
-- ==============================================
CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    status ENUM('active','expired','canceled') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ==============================================
-- 12. Aktivite Logları Tablosu
-- ==============================================

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT DEFAULT NULL,            -- Logu oluşturan kullanıcı (sistem için NULL olabilir)
    action VARCHAR(100) NOT NULL,        -- Yapılan eylem (örn: "add_product", "update_storage")
    table_name VARCHAR(50) DEFAULT NULL, -- Hangi tabloyu etkiledi (örn: "products", "storages")
    record_id INT DEFAULT NULL,          -- Etkilenen kayıt ID (örn: product_id)
    ip_address VARCHAR(50) DEFAULT NULL, -- Kullanıcının IP adresi
    user_agent VARCHAR(255) DEFAULT NULL,-- Kullanıcının cihaz/browser bilgisi
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ==============================================
-- 1. Diller Başlangıç Verisi
-- ==============================================
INSERT INTO languages (code, name) VALUES
('tr', 'Türkçe'),
('en', 'English');

-- ==============================================
-- 2. Ölçü Birimleri Ana Tablosu
-- ==============================================
INSERT INTO units (code) VALUES
('KG'),   -- kilogram
('LT'),   -- litre
('PCS');  -- adet

-- ==============================================
-- 3. Ölçü Birimi Çevirileri (unit_translations)
-- ==============================================
-- KG
INSERT INTO unit_translations (unit_id, language_id, name, abbreviation) VALUES
(1, 1, 'Kilogram', 'kg'),  -- Türkçe
(1, 2, 'Kilogram', 'kg');  -- English

-- LT
INSERT INTO unit_translations (unit_id, language_id, name, abbreviation) VALUES
(2, 1, 'Litre', 'L'),   -- Türkçe
(2, 2, 'Liter', 'L');   -- English

-- PCS
INSERT INTO unit_translations (unit_id, language_id, name, abbreviation) VALUES
(3, 1, 'Adet', 'adet'), -- Türkçe
(3, 2, 'Piece', 'pcs');

INSERT INTO family_members (user_id, member_user_id, role) VALUES
(1, 2, 'sub');

-- ==============================================
-- 6. Örnek Depo
-- ==============================================
INSERT INTO storages (user_id, name) VALUES
(1, 'Buzdolabı');

-- ==============================================
-- 7. Örnek Ürünler
-- ==============================================
INSERT INTO products (storage_id, name, quantity, unit_id, expiration_date) VALUES
(1, 'Süt', 2, 2, '2025-09-20'),    -- 2 litre süt
(1, 'Elma', 5, 3, '2025-09-15'),   -- 5 adet elma
(1, 'Tereyağı', 1, 1, '2025-10-01'); -- 1 kg tereyağı

-- ==============================================
-- 8. Örnek Alışveriş Listesi
-- ==============================================
INSERT INTO shopping_lists (user_id, name) VALUES
(1, 'Haftalık Alışveriş');

-- ==============================================
-- 9. Örnek Alışveriş Listesi Ürünleri
-- ==============================================
INSERT INTO shopping_list_items (shopping_list_id, product_name, quantity, unit_id) VALUES
(1, 'Süt', 3, 2),
(1, 'Ekmek', 2, 3),
(1, 'Elma', 6, 3);

-- ==============================================
-- 10. Örnek Abonelik
-- ==============================================
INSERT INTO subscriptions (user_id, start_date, end_date, status) VALUES
(1, '2025-09-10 00:00:00', '2026-09-10 23:59:59', 'active');
