init_schema.sql

-- ================================================================
-- ENTERPRISE EQUIPMENT RENTAL SYSTEM - DATABASE SCHEMA
-- Version: 2.1 (Phase 4 Ready: Pricing, Contracts & Revenue Recognition)
-- Dialect: PostgreSQL
-- ================================================================

-- 1. CLEANUP: Drop existing tables and sequences to ensure a clean state
DROP TABLE IF EXISTS revenue_recognitions CASCADE;
DROP TABLE IF EXISTS contracts CASCADE;
DROP TABLE IF EXISTS portable_tools CASCADE;
DROP TABLE IF EXISTS heavy_machinery CASCADE;
DROP TABLE IF EXISTS equipment CASCADE;
DROP TABLE IF EXISTS customers CASCADE;
DROP TABLE IF EXISTS sys_locks CASCADE;
DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP SEQUENCE IF EXISTS equipment_seq;

-- ================================================================
-- MODULE A: SECURITY & USERS
-- ================================================================
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('ADMIN', 'MANAGER')),
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE
);

-- Default system administrator (Password should be hashed in production)
INSERT INTO users (username, password_hash, role, full_name)
VALUES ('admin', '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9', 'ADMIN', 'System Administrator');

-- ================================================================
-- MODULE B: CUSTOMERS (Single Table Inheritance - STI)
-- ================================================================
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    type VARCHAR(20) NOT NULL CHECK (type IN ('INDIVIDUAL', 'COMPANY')),
    name VARCHAR(150) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),

    -- STI Specific Fields
    tax_id VARCHAR(20),           -- Used for COMPANY type
    passport_no VARCHAR(20),      -- Used for INDIVIDUAL type
    discount_rate DECIMAL(5, 2) DEFAULT 0.0, -- Loyalty/VIP discount

    is_active BOOLEAN DEFAULT TRUE,
    
    -- Concurrency Control
    version INT DEFAULT 0 NOT NULL,

    -- Data Integrity Constraints for STI
    CONSTRAINT chk_customer_sti_integrity CHECK (
        (type = 'INDIVIDUAL' AND tax_id IS NULL) OR
        (type = 'COMPANY' AND passport_no IS NULL)
    )
);

-- ================================================================
-- MODULE C: INVENTORY (Class Table Inheritance - CTI)
-- ================================================================
CREATE SEQUENCE equipment_seq START WITH 1000 INCREMENT BY 1;

-- Parent table for all equipment types
CREATE TABLE equipment (
    id BIGINT PRIMARY KEY, 
    inventory_id VARCHAR(50) NOT NULL UNIQUE,
    model_name VARCHAR(100) NOT NULL,
    daily_rate_amount DECIMAL(19, 4) NOT NULL, -- Embedded Value pattern
    daily_rate_currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    acquisition_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL CHECK (status IN ('AVAILABLE', 'RENTED', 'IN_MAINTENANCE', 'DECOMMISSIONED')),
    version INT DEFAULT 0 NOT NULL
);

-- Child table for heavy machinery (engine hours & overtime logic)
CREATE TABLE heavy_machinery (
    id BIGINT PRIMARY KEY,
    engine_hours DOUBLE PRECISION DEFAULT 0.0,
    fuel_capacity DOUBLE PRECISION NOT NULL,
    included_hours_per_day DOUBLE PRECISION DEFAULT 8.0, -- Pricing Strategy param
    overtime_multiplier DOUBLE PRECISION DEFAULT 1.5,   -- Pricing Strategy param
    CONSTRAINT fk_heavy_eq FOREIGN KEY (id) REFERENCES equipment(id) ON DELETE CASCADE
);

-- Child table for smaller portable tools
CREATE TABLE portable_tools (
    id BIGINT PRIMARY KEY,
    power_source VARCHAR(30) CHECK (power_source IN ('ELECTRIC', 'BATTERY', 'GAS')),
    requires_consumables BOOLEAN DEFAULT FALSE,
    next_safety_check_date DATE,
    CONSTRAINT fk_tool_eq FOREIGN KEY (id) REFERENCES equipment(id) ON DELETE CASCADE
);

-- ================================================================
-- MODULE D: RENTAL CONTRACTS
-- ================================================================
CREATE TABLE contracts (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    equipment_id BIGINT NOT NULL REFERENCES equipment(id),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    actual_return_date DATE,
    
    -- Counters fixed at the start and end of rental
    start_engine_hours DOUBLE PRECISION,
    end_engine_hours DOUBLE PRECISION,
    
    -- Financial fields (calculated upon return)
    total_cost_amount DECIMAL(19, 4),
    total_cost_currency VARCHAR(3) DEFAULT 'USD',
    
    status VARCHAR(20) CHECK (status IN ('DRAFT', 'ACTIVE', 'CLOSED', 'CANCELLED')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version INT DEFAULT 0 NOT NULL
);

-- ================================================================
-- MODULE E: FINANCE (Revenue Recognition Pattern)
-- ================================================================
-- Table to store revenue recognized per specific date
CREATE TABLE revenue_recognitions (
    id BIGSERIAL PRIMARY KEY,
    contract_id BIGINT NOT NULL REFERENCES contracts(id),
    amount DECIMAL(19, 4) NOT NULL,
    recognized_date DATE NOT NULL
);

-- ================================================================
-- MODULE F: SYSTEM INFRASTRUCTURE (Locking & Audit)
-- ================================================================
-- Pessimistic locking support if needed
CREATE TABLE sys_locks (
    lock_id VARCHAR(36) PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    creation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiration_time TIMESTAMP NOT NULL,
    CONSTRAINT uq_lock UNIQUE (table_name, record_id)
);

-- Centralized audit log for security and monitoring
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    entity_id BIGINT,
    details TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);