-- Create database
CREATE DATABASE IF NOT EXISTS yorem_reklam CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE yorem_reklam;

-- Advertising units table
CREATE TABLE IF NOT EXISTS advertising_units (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('billboard', 'billboard_plus', 'megalight', 'clp') NOT NULL,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(255) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    description TEXT,
    status ENUM('available', 'occupied') DEFAULT 'available',
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers table
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(100) NOT NULL,
    contact_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reservations table
CREATE TABLE IF NOT EXISTS reservations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    unit_id INT NOT NULL,
    customer_id INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES advertising_units(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Contracts table
CREATE TABLE IF NOT EXISTS contracts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    reservation_id INT NOT NULL,
    contract_file VARCHAR(255) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_status ENUM('pending', 'partial', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reservation_id) REFERENCES reservations(id)
);

-- Payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    contract_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contracts(id)
);

-- Notifications table
CREATE TABLE IF NOT EXISTS notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample advertising units
INSERT INTO advertising_units (type, name, location, latitude, longitude, description) VALUES
('billboard', 'A1 Billboard', 'İstanbul Kadıköy', 40.9909, 29.0307, 'A1 Karayolu üzerinde billboard'),
('billboard_plus', 'B1 Plus Billboard', 'İstanbul Beşiktaş', 41.0422, 29.0083, 'B1 Plus özel billboard'),
('megalight', 'M1 Megalight', 'İstanbul Şişli', 41.0602, 28.9877, 'M1 Megalight reklam alanı'),
('clp', 'C1 CLP', 'İstanbul Üsküdar', 41.0235, 29.0145, 'C1 CLP reklam alanı'); 