-- Finans Takip - cPanel / phpMyAdmin uyumlu veritabani semasi
-- ONEMLI: Bu dosyada CREATE DATABASE ve USE komutlari YOKTUR.
-- phpMyAdmin'de once cPanel'in olusturdugu veritabanini secip bu dosyayi Import edin.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS transactions (
 id INT AUTO_INCREMENT PRIMARY KEY,
 type ENUM('income','expense') NOT NULL,
 category VARCHAR(60) NOT NULL,
 title VARCHAR(255) NOT NULL,
 company VARCHAR(255) NULL,
 amount DECIMAL(14,2) NOT NULL,
 due_date DATE NOT NULL,
 status ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
 recurring ENUM('none','monthly','yearly') NOT NULL DEFAULT 'none',
 account VARCHAR(120) NULL,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_due_date(due_date),
 INDEX idx_status(status),
 INDEX idx_type(type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS credit_cards (
 id INT AUTO_INCREMENT PRIMARY KEY,
 bank VARCHAR(100) NOT NULL,
 card_name VARCHAR(100) NOT NULL,
 statement_day TINYINT NOT NULL,
 due_day TINYINT NOT NULL,
 current_debt DECIMAL(14,2) DEFAULT 0,
 minimum_payment DECIMAL(14,2) DEFAULT 0,
 active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

CREATE TABLE IF NOT EXISTS loans (
 id INT AUTO_INCREMENT PRIMARY KEY,
 bank VARCHAR(100) NOT NULL,
 loan_name VARCHAR(150) NOT NULL,
 installment DECIMAL(14,2) NOT NULL,
 total_installments INT DEFAULT 0,
 remaining_installments INT DEFAULT 0,
 next_due_date DATE NULL,
 remaining_principal DECIMAL(14,2) DEFAULT 0,
 active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

SET FOREIGN_KEY_CHECKS=1;
