CREATE DATABASE IF NOT EXISTS emlakekspertizi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE emlakekspertizi;
CREATE TABLE IF NOT EXISTS provinces (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(128) NOT NULL,region VARCHAR(64) NULL,UNIQUE KEY uq_province_name (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS districts (id INT AUTO_INCREMENT PRIMARY KEY,province_id INT NOT NULL,name VARCHAR(128) NOT NULL,FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,UNIQUE KEY uq_district (province_id, name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS neighborhoods (id INT AUTO_INCREMENT PRIMARY KEY,district_id INT NOT NULL,name VARCHAR(128) NOT NULL,code VARCHAR(32) NULL,FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE,UNIQUE KEY uq_neighborhood (district_id, name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS price_index (id BIGINT AUTO_INCREMENT PRIMARY KEY,geo_level ENUM('province','district','neighborhood') NOT NULL,geo_id INT NOT NULL,category ENUM('satilik_konut','kiralik_konut','arsa','isyeri') NOT NULL DEFAULT 'satilik_konut',period DATE NOT NULL,value_decimal DECIMAL(12,2) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_index (geo_level, geo_id, category, period),KEY idx_period (period)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY,email VARCHAR(191) NOT NULL UNIQUE,password_hash VARCHAR(191) NOT NULL,role ENUM('free','gold','platinum','admin') NOT NULL DEFAULT 'free',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS reports (id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,geo_level ENUM('province','district','neighborhood') NOT NULL,geo_id INT NOT NULL,category ENUM('satilik_konut','kiralik_konut','arsa','isyeri') NOT NULL DEFAULT 'satilik_konut',period_from DATE NOT NULL,period_to DATE NOT NULL,score DECIMAL(6,2) NULL,pdf_url VARCHAR(512) NULL,qr_hash VARCHAR(128) NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;