CREATE DATABASE billing_system;
USE billing_system;

-- Обекти за охрана
CREATE TABLE facilities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    address TEXT NOT NULL,
    type VARCHAR(100),
    client_id INT,
    FOREIGN KEY (client_id) REFERENCES users(id)
);

-- Договори
CREATE TABLE contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_number VARCHAR(50) UNIQUE NOT NULL,
    client_id INT,
    facility_id INT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    monthly_fee DECIMAL(10,2) NOT NULL,
    status ENUM('active', 'expired', 'terminated') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES users(id),
    FOREIGN KEY (facility_id) REFERENCES facilities(id)
);

-- Плащания
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT,
    amount DECIMAL(10,2) NOT NULL,
    payment_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('paid', 'pending', 'overdue') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (contract_id) REFERENCES contracts(id)
);

INSERT INTO facilities (name, address, type, client_id) VALUES 
('Офис сграда', 'ул. Витоша 1, София', 'Офис сграда', 2),
('Търговски център', 'ул. Марица 15, Пловдив', 'Търговски обект', 3);

INSERT INTO contracts (contract_number, client_id, facility_id, start_date, end_date, monthly_fee, status) VALUES 
('K-2024-001', 2, 1, '2024-01-01', '2024-12-31', 1500.00, 'active'),
('K-2024-002', 3, 2, '2024-06-01', '2025-05-31', 2200.00, 'active');