-- 004_actual.sql — tabel realisasi/aktual (WP-0B)

CREATE TABLE gl_entry (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    entry_date DATE NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    description VARCHAR(255) NULL,
    ref_no VARCHAR(40) NULL,
    debit DECIMAL(18,2) NOT NULL DEFAULT 0,
    credit DECIMAL(18,2) NOT NULL DEFAULT 0,
    source VARCHAR(40) NULL,
    imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_gl_entry_date (entry_date),
    KEY idx_gl_entry_account (account_id),
    CONSTRAINT fk_gl_entry_account FOREIGN KEY (account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE actual_monthly (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    year SMALLINT NOT NULL,
    month TINYINT NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    debit DECIMAL(18,2) NOT NULL DEFAULT 0,
    credit DECIMAL(18,2) NOT NULL DEFAULT 0,
    net DECIMAL(18,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_actual_monthly (year, month, account_id),
    KEY idx_actual_monthly_account (account_id),
    CONSTRAINT fk_actual_monthly_account FOREIGN KEY (account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE opening_balance (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    year SMALLINT NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_opening_balance (year, account_id),
    KEY idx_opening_balance_account (account_id),
    CONSTRAINT fk_opening_balance_account FOREIGN KEY (account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stat_actual (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    year SMALLINT NOT NULL,
    month TINYINT NOT NULL,
    stat_code VARCHAR(40) NOT NULL,
    value DECIMAL(18,6) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_stat_actual (year, month, stat_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE period_lock (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    year SMALLINT NOT NULL,
    month TINYINT NOT NULL,
    locked_by INT UNSIGNED NULL,
    locked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_period_lock (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE res_booking (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    booking_date DATE NOT NULL,
    stay_date DATE NOT NULL,
    segment VARCHAR(40) NULL,
    room_type VARCHAR(60) NULL,
    country VARCHAR(60) NULL,
    nights INT NOT NULL DEFAULT 1,
    amount DECIMAL(18,2) NOT NULL DEFAULT 0,
    source VARCHAR(40) NULL,
    PRIMARY KEY (id),
    KEY idx_res_booking_stay_date (stay_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pickup_curve (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    lead_bucket VARCHAR(20) NOT NULL,
    pct_booked DECIMAL(6,3) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_pickup_curve_bucket (lead_bucket)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;