-- =====================================================================
-- TIMOPDF - Tourism Proposal PDF System
-- Database schema (MySQL 5.7+/MariaDB 10.3+)
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150) NOT NULL,
    email           VARCHAR(190) NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    role            ENUM('admin','editor') NOT NULL DEFAULT 'admin',
    avatar          VARCHAR(255) NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Companies (single-row settings table)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS companies;
CREATE TABLE companies (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_en             VARCHAR(190) NOT NULL,
    name_ar             VARCHAR(190) NULL,
    logo                VARCHAR(255) NULL,
    email               VARCHAR(190) NULL,
    phone               VARCHAR(80)  NULL,
    address_en          TEXT NULL,
    address_ar          TEXT NULL,
    website             VARCHAR(190) NULL,
    default_header_en   VARCHAR(255) NULL,
    default_header_ar   VARCHAR(255) NULL,
    default_subheading_en VARCHAR(255) NULL,
    default_subheading_ar VARCHAR(255) NULL,
    brand_primary       VARCHAR(20) NOT NULL DEFAULT '#0B2A6B',
    brand_secondary     VARCHAR(20) NOT NULL DEFAULT '#1E4FD8',
    brand_accent        VARCHAR(20) NOT NULL DEFAULT '#C9A55C',
    pdf_theme           VARCHAR(50) NOT NULL DEFAULT 'royal-blue',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Currencies
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS currencies;
CREATE TABLE currencies (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(8)  NOT NULL UNIQUE,
    name_en     VARCHAR(80) NOT NULL,
    name_ar     VARCHAR(80) NULL,
    symbol      VARCHAR(10) NULL,
    status      TINYINT(1)  NOT NULL DEFAULT 1,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Cities
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_en     VARCHAR(150) NOT NULL,
    name_ar     VARCHAR(150) NULL,
    status      TINYINT(1) NOT NULL DEFAULT 1,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Hotels
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS hotels;
CREATE TABLE hotels (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    city_id     INT UNSIGNED NOT NULL,
    name_en     VARCHAR(190) NOT NULL,
    name_ar     VARCHAR(190) NULL,
    address_en  TEXT NULL,
    address_ar  TEXT NULL,
    website     VARCHAR(190) NULL,
    stars       TINYINT UNSIGNED NULL DEFAULT NULL,
    image       VARCHAR(255) NULL,
    status      TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_hotels_city (city_id),
    CONSTRAINT fk_hotels_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Places / Attractions
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS places;
CREATE TABLE places (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    city_id         INT UNSIGNED NOT NULL,
    name_en         VARCHAR(190) NOT NULL,
    name_ar         VARCHAR(190) NULL,
    description_en  TEXT NULL,
    description_ar  TEXT NULL,
    image           VARCHAR(255) NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_places_city (city_id),
    CONSTRAINT fk_places_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Transportation types (master list: Flight, Bus, Train, Other)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS transportation_types;
CREATE TABLE transportation_types (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_en     VARCHAR(80) NOT NULL,
    name_ar     VARCHAR(80) NULL,
    icon        VARCHAR(50) NULL,
    status      TINYINT(1) NOT NULL DEFAULT 1,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Inclusions master list
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS inclusions;
CREATE TABLE inclusions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_en        VARCHAR(190) NOT NULL,
    title_ar        VARCHAR(190) NULL,
    description_en  TEXT NULL,
    description_ar  TEXT NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Exclusions master list
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS exclusions;
CREATE TABLE exclusions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_en        VARCHAR(190) NOT NULL,
    title_ar        VARCHAR(190) NULL,
    description_en  TEXT NULL,
    description_ar  TEXT NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Cancellation policies
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS cancellation_policies;
CREATE TABLE cancellation_policies (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_en        VARCHAR(190) NOT NULL,
    title_ar        VARCHAR(190) NULL,
    description_en  TEXT NULL,
    description_ar  TEXT NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Payment methods
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS payment_methods;
CREATE TABLE payment_methods (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title_en        VARCHAR(190) NOT NULL,
    title_ar        VARCHAR(190) NULL,
    description_en  TEXT NULL,
    description_ar  TEXT NULL,
    status          TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposals (root entity)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposals;
CREATE TABLE proposals (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_number     VARCHAR(50)  NOT NULL UNIQUE,
    proposal_type       ENUM('individual','corporate') NOT NULL DEFAULT 'individual',
    language_mode       ENUM('en','ar','bilingual') NOT NULL DEFAULT 'en',
    currency_id         INT UNSIGNED NULL,
    company_id          INT UNSIGNED NULL,
    title_en            VARCHAR(255) NULL,
    title_ar            VARCHAR(255) NULL,
    header_en           VARCHAR(255) NULL,
    header_ar           VARCHAR(255) NULL,
    subheading_en       VARCHAR(255) NULL,
    subheading_ar       VARCHAR(255) NULL,
    company_name_en     VARCHAR(190) NULL,
    company_name_ar     VARCHAR(190) NULL,
    trip_from_en        VARCHAR(150) NULL,
    trip_from_ar        VARCHAR(150) NULL,
    trip_to_en          VARCHAR(150) NULL,
    trip_to_ar          VARCHAR(150) NULL,
    days_count          INT UNSIGNED NOT NULL DEFAULT 0,
    nights_count        INT UNSIGNED NOT NULL DEFAULT 0,
    cover_background    VARCHAR(255) NULL,
    total_price         DECIMAL(14,2) NOT NULL DEFAULT 0,
    cancellation_policy_id INT UNSIGNED NULL,
    payment_method_id   INT UNSIGNED NULL,
    payment_description_en TEXT NULL,
    payment_description_ar TEXT NULL,
    additional_notes_en TEXT NULL,
    additional_notes_ar TEXT NULL,
    status              ENUM('draft','exported','approved','archived') NOT NULL DEFAULT 'draft',
    version             INT UNSIGNED NOT NULL DEFAULT 1,
    created_by          INT UNSIGNED NULL,
    updated_by          INT UNSIGNED NULL,
    last_exported_at    DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_proposals_status (status),
    KEY idx_proposals_currency (currency_id),
    CONSTRAINT fk_proposals_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL,
    CONSTRAINT fk_proposals_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL,
    CONSTRAINT fk_proposals_policy FOREIGN KEY (cancellation_policy_id) REFERENCES cancellation_policies(id) ON DELETE SET NULL,
    CONSTRAINT fk_proposals_payment FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id) ON DELETE SET NULL,
    CONSTRAINT fk_proposals_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_proposals_updater FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal client (1-to-1 with proposals)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_clients;
CREATE TABLE proposal_clients (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id         INT UNSIGNED NOT NULL,
    client_type         ENUM('individual','corporate') NOT NULL,
    name                VARCHAR(190) NULL,
    corporate_name      VARCHAR(190) NULL,
    contact_person_name VARCHAR(190) NULL,
    phone               VARCHAR(80)  NULL,
    email               VARCHAR(190) NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_pc_proposal (proposal_id),
    CONSTRAINT fk_pc_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal hotels by city (selected hotels grouped by city)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_hotels;
CREATE TABLE proposal_hotels (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id  INT UNSIGNED NOT NULL,
    city_id      INT UNSIGNED NOT NULL,
    hotel_id     INT UNSIGNED NOT NULL,
    nights       INT UNSIGNED NOT NULL DEFAULT 0,
    sort_order   INT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_ph_proposal (proposal_id),
    CONSTRAINT fk_ph_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE,
    CONSTRAINT fk_ph_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE,
    CONSTRAINT fk_ph_hotel FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal days (itinerary)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_days;
CREATE TABLE proposal_days (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id   INT UNSIGNED NOT NULL,
    day_number    INT UNSIGNED NOT NULL,
    city_id       INT UNSIGNED NULL,
    title_en      VARCHAR(255) NULL,
    title_ar      VARCHAR(255) NULL,
    notes_en      TEXT NULL,
    notes_ar      TEXT NULL,
    sort_order    INT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_pd_proposal (proposal_id),
    CONSTRAINT fk_pd_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE,
    CONSTRAINT fk_pd_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal day places (places visited per day)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_day_places;
CREATE TABLE proposal_day_places (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_day_id    INT UNSIGNED NOT NULL,
    place_id           INT UNSIGNED NOT NULL,
    description_override_en TEXT NULL,
    description_override_ar TEXT NULL,
    sort_order         INT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_pdp_day (proposal_day_id),
    CONSTRAINT fk_pdp_day FOREIGN KEY (proposal_day_id) REFERENCES proposal_days(id) ON DELETE CASCADE,
    CONSTRAINT fk_pdp_place FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal day transportation
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_day_transportations;
CREATE TABLE proposal_day_transportations (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_day_id     INT UNSIGNED NOT NULL,
    transport_type_id   INT UNSIGNED NOT NULL,
    from_location_en    VARCHAR(150) NULL,
    from_location_ar    VARCHAR(150) NULL,
    to_location_en      VARCHAR(150) NULL,
    to_location_ar      VARCHAR(150) NULL,
    price               DECIMAL(14,2) NULL,
    currency_id         INT UNSIGNED NULL,
    notes_en            TEXT NULL,
    notes_ar            TEXT NULL,
    KEY idx_pdt_day (proposal_day_id),
    CONSTRAINT fk_pdt_day FOREIGN KEY (proposal_day_id) REFERENCES proposal_days(id) ON DELETE CASCADE,
    CONSTRAINT fk_pdt_type FOREIGN KEY (transport_type_id) REFERENCES transportation_types(id) ON DELETE RESTRICT,
    CONSTRAINT fk_pdt_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal inclusions
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_inclusions;
CREATE TABLE proposal_inclusions (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id       INT UNSIGNED NOT NULL,
    inclusion_id      INT UNSIGNED NOT NULL,
    custom_title_en   VARCHAR(255) NULL,
    custom_title_ar   VARCHAR(255) NULL,
    description_en    TEXT NULL,
    description_ar    TEXT NULL,
    price             DECIMAL(14,2) NULL,
    currency_id       INT UNSIGNED NULL,
    sort_order        INT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_pi_proposal (proposal_id),
    CONSTRAINT fk_pi_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE,
    CONSTRAINT fk_pi_inclusion FOREIGN KEY (inclusion_id) REFERENCES inclusions(id) ON DELETE CASCADE,
    CONSTRAINT fk_pi_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal exclusions
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_exclusions;
CREATE TABLE proposal_exclusions (
    id                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id       INT UNSIGNED NOT NULL,
    exclusion_id      INT UNSIGNED NOT NULL,
    custom_title_en   VARCHAR(255) NULL,
    custom_title_ar   VARCHAR(255) NULL,
    description_en    TEXT NULL,
    description_ar    TEXT NULL,
    price             DECIMAL(14,2) NULL,
    currency_id       INT UNSIGNED NULL,
    sort_order        INT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_pe_proposal (proposal_id),
    CONSTRAINT fk_pe_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE,
    CONSTRAINT fk_pe_exclusion FOREIGN KEY (exclusion_id) REFERENCES exclusions(id) ON DELETE CASCADE,
    CONSTRAINT fk_pe_currency FOREIGN KEY (currency_id) REFERENCES currencies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Proposal versions (history snapshot)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS proposal_versions;
CREATE TABLE proposal_versions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    proposal_id     INT UNSIGNED NOT NULL,
    version         INT UNSIGNED NOT NULL,
    snapshot        LONGTEXT NOT NULL,
    pdf_path        VARCHAR(255) NULL,
    action          ENUM('saved','exported','approved') NOT NULL DEFAULT 'saved',
    created_by      INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_pv_proposal (proposal_id),
    CONSTRAINT fk_pv_proposal FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE CASCADE,
    CONSTRAINT fk_pv_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Activity log (audit)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS activity_log;
CREATE TABLE activity_log (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NULL,
    action       VARCHAR(80)  NOT NULL,
    subject_type VARCHAR(80)  NULL,
    subject_id   INT UNSIGNED NULL,
    description  TEXT NULL,
    ip_address   VARCHAR(64) NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_al_subject (subject_type, subject_id),
    CONSTRAINT fk_al_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
