-- Ajans Reklam Bütçe Planlayıcı Veritabanı Şeması

-- Veritabanı oluşturma (Eğer yetkiniz varsa)
-- CREATE DATABASE IF NOT EXISTS agency_budget_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE agency_budget_db;

-- 1. Users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `role` ENUM('admin', 'user') DEFAULT 'admin',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Clients table
CREATE TABLE IF NOT EXISTS `clients` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `company_name` VARCHAR(255) NOT NULL,
    `industry` VARCHAR(100),
    `website` VARCHAR(255),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Regions table
CREATE TABLE IF NOT EXISTS `regions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `google_target_id` BIGINT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Client Regions (Many-to-Many)
CREATE TABLE IF NOT EXISTS `client_regions` (
    `client_id` INT,
    `region_id` INT,
    PRIMARY KEY (`client_id`, `region_id`),
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Keywords table
CREATE TABLE IF NOT EXISTS `keywords` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT,
    `keyword` VARCHAR(255) NOT NULL,
    `match_type` ENUM('broad', 'phrase', 'exact') DEFAULT 'broad',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Proposals table
CREATE TABLE IF NOT EXISTS `proposals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `client_id` INT,
    `title` VARCHAR(255),
    `total_budget` DECIMAL(15, 2),
    `google_budget` DECIMAL(15, 2),
    `meta_budget` DECIMAL(15, 2),
    `estimated_clicks` INT,
    `estimated_impressions` INT,
    `pdf_path` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Google Metrics Cache
CREATE TABLE IF NOT EXISTS `google_metrics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `keyword_id` INT,
    `region_id` INT,
    `avg_monthly_searches` INT,
    `competition` VARCHAR(50),
    `bid_low` DECIMAL(10, 2),
    `bid_high` DECIMAL(10, 2),
    `avg_cpc` DECIMAL(10, 2),
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`keyword_id`) REFERENCES `keywords`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`region_id`) REFERENCES `regions`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 8. Meta Metrics Cache/Benchmark
CREATE TABLE IF NOT EXISTS `meta_metrics` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `industry` VARCHAR(100),
    `cpm` DECIMAL(10, 2),
    `cpc` DECIMAL(10, 2),
    `ctr` DECIMAL(5, 2),
    `reach` INT,
    `impressions` INT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 9. API Credentials
CREATE TABLE IF NOT EXISTS `api_credentials` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_name` VARCHAR(50) NOT NULL UNIQUE,
    `credentials` JSON,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 10. API Logs
CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `service_name` VARCHAR(50),
    `endpoint` VARCHAR(255),
    `request_data` JSON,
    `response_data` JSON,
    `status_code` INT,
    `error_message` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Varsayılan verileri ekle
INSERT INTO `regions` (`name`, `google_target_id`) VALUES 
('İstanbul', 1012782), ('Ankara', 1012763), ('İzmir', 1012770), ('Bursa', 1012765), 
('Antalya', 1012764), ('Adana', 1012761), ('Konya', 1012773), ('Gaziantep', 1012767), 
('Şanlıurfa', 1012781), ('Kocaeli', 1012772), ('Mersin', 1012776), ('Diyarbakır', 1012766), 
('Hatay', 1012768), ('Manisa', 1012775), ('Kayseri', 1012771), ('Samsun', 1012779), 
('Balıkesir', 1012769), ('Kahramanmaraş', 1012774), ('Van', 1012783), ('Aydın', 1012762);

-- Varsayılan admin (şifre: admin123)
INSERT INTO `users` (`username`, `password`, `email`, `role`) VALUES 
('admin', '$2y$10$QOwsZfw/bE7eHij5wY/3G.ebtC8Zi19ylM62X2c9Nv5rMSGMWtzZe', 'admin@agency.com', 'admin');