-- Kullanıcı girişi + her kullanıcı kendi verisi (sıfır sayfa)
-- phpMyAdmin -> SQL sekmesinde bu dosyayı çalıştırın. Tekrar çalıştırırsanız "Duplicate column" hatası vermez.

-- 1) Kullanıcılar tablosu
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2) Veri tablolarına user_id ekle (sütun yoksa ekler; varsa atlar)
DELIMITER //
DROP PROCEDURE IF EXISTS add_user_id_if_missing//
CREATE PROCEDURE add_user_id_if_missing()
BEGIN
  IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'income_categories' AND COLUMN_NAME = 'user_id') = 0 THEN
    ALTER TABLE income_categories ADD COLUMN user_id INT UNSIGNED NULL DEFAULT NULL AFTER id;
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'income_entries' AND COLUMN_NAME = 'user_id') = 0 THEN
    ALTER TABLE income_entries ADD COLUMN user_id INT UNSIGNED NULL DEFAULT NULL AFTER id;
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'expense_categories' AND COLUMN_NAME = 'user_id') = 0 THEN
    ALTER TABLE expense_categories ADD COLUMN user_id INT UNSIGNED NULL DEFAULT NULL AFTER id;
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'expenses' AND COLUMN_NAME = 'user_id') = 0 THEN
    ALTER TABLE expenses ADD COLUMN user_id INT UNSIGNED NULL DEFAULT NULL AFTER id;
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cari_movements' AND COLUMN_NAME = 'user_id') = 0 THEN
    ALTER TABLE cari_movements ADD COLUMN user_id INT UNSIGNED NULL DEFAULT NULL AFTER id;
  END IF;
END//
DELIMITER ;
CALL add_user_id_if_missing();
DROP PROCEDURE add_user_id_if_missing;

-- İndeksler (yoksa oluşturur)
DELIMITER //
DROP PROCEDURE IF EXISTS add_user_indexes_if_missing//
CREATE PROCEDURE add_user_indexes_if_missing()
BEGIN
  IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'income_categories' AND INDEX_NAME = 'idx_income_categories_user') = 0 THEN
    CREATE INDEX idx_income_categories_user ON income_categories(user_id);
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'income_entries' AND INDEX_NAME = 'idx_income_entries_user') = 0 THEN
    CREATE INDEX idx_income_entries_user ON income_entries(user_id);
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'expense_categories' AND INDEX_NAME = 'idx_expense_categories_user') = 0 THEN
    CREATE INDEX idx_expense_categories_user ON expense_categories(user_id);
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'expenses' AND INDEX_NAME = 'idx_expenses_user') = 0 THEN
    CREATE INDEX idx_expenses_user ON expenses(user_id);
  END IF;
  IF (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'cari_movements' AND INDEX_NAME = 'idx_cari_movements_user') = 0 THEN
    CREATE INDEX idx_cari_movements_user ON cari_movements(user_id);
  END IF;
END//
DELIMITER ;
CALL add_user_indexes_if_missing();
DROP PROCEDURE add_user_indexes_if_missing;

-- 3) İlk kullanıcılar (emo, yes) - zaten varsa atlanır
INSERT IGNORE INTO users (id, username, password_hash) VALUES
  (1, 'emo', ''),
  (2, 'yes', '');
