CREATE DATABASE IF NOT EXISTS ms_foto_muhasebe CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;
USE ms_foto_muhasebe;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(100) NOT NULL, username VARCHAR(50) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL, role ENUM('admin','user') NOT NULL DEFAULT 'user', is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE income_categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, is_active TINYINT(1) NOT NULL DEFAULT 1);
INSERT INTO income_categories (name) VALUES ('Vesikalık'),('Biyometrik'),('Çerçeve'),('Düğün'),('Diğer');
CREATE TABLE transactions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, transaction_type ENUM('income','expense') NOT NULL, category_id INT UNSIGNED NULL,
 description VARCHAR(255) NOT NULL, amount DECIMAL(12,2) NOT NULL, transaction_date DATE NOT NULL,
 created_by INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (category_id) REFERENCES income_categories(id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES users(id), INDEX(transaction_date), INDEX(transaction_type)
) ENGINE=InnoDB;
CREATE TABLE investments (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, description VARCHAR(255) NOT NULL, amount DECIMAL(12,2) NOT NULL, investment_date DATE NOT NULL,
 created_by INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (created_by) REFERENCES users(id), INDEX(investment_date)
) ENGINE=InnoDB;
CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, action VARCHAR(40) NOT NULL, entity VARCHAR(40) NOT NULL,
 entity_id INT UNSIGNED NULL, detail VARCHAR(500) NULL, ip_address VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id), INDEX(created_at), INDEX(user_id)
) ENGINE=InnoDB;