

-- 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;

-- 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);
