---

-- 1. Таблица категорий (categories)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    icon VARCHAR(255)
);

-- 2. Таблица курсов (courses)
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    current_discount DECIMAL(10, 2) DEFAULT 0,
    is_available BOOLEAN DEFAULT TRUE,
    category_id INTEGER,
    CONSTRAINT fk_courses_category FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 3. Таблица пользователей (users)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    is_premium BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. Таблица чеков (receipts)
CREATE TABLE receipts (
    id SERIAL PRIMARY KEY,
    receipt_url TEXT
);

-- 5. Таблица связи (user_courses)
-- Внимание: здесь составной первичный ключ, автоувеличение для него не применяется.
CREATE TABLE user_courses (
    user_id INTEGER,
    course_id INTEGER,
    receipt_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, course_id),
    CONSTRAINT fk_uc_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_uc_course FOREIGN KEY (course_id) REFERENCES courses(id),
    CONSTRAINT fk_uc_receipt FOREIGN KEY (receipt_id) REFERENCES receipts(id)
);