-- Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15),
    CreatedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (Email)
);

-- Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    CategoryID INT,
    Price DECIMAL(10,2) NOT NULL,
    StockQuantity INT DEFAULT 0,
    Description TEXT,
    IsActive BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID),
    INDEX idx_category (CategoryID),
    INDEX idx_active (IsActive)
);

-- Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(12,2) NOT NULL,
    OrderStatus ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    INDEX idx_customer (CustomerID),
    INDEX idx_date (OrderDate),
    INDEX idx_status (OrderStatus)
);

-- Order Items Junction Table
CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Denormalized Customer Order Summary
CREATE TABLE CustomerOrderSummary (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    TotalOrders INT DEFAULT 0,
    TotalSpent DECIMAL(12,2) DEFAULT 0.00,
    LastOrderDate TIMESTAMP,
    AverageOrderValue DECIMAL(10,2),
    PreferredCategory VARCHAR(50),
    -- Denormalized for reporting performance
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Performance Indexes
CREATE INDEX idx_orders_customer_date ON Orders(CustomerID, OrderDate);
CREATE INDEX idx_products_category_price ON Products(CategoryID, Price);
CREATE INDEX idx_customers_email_active ON Customers(Email, IsActive);

-- Composite Index for Common Query Patterns
CREATE INDEX idx_order_items_product_order ON OrderItems(ProductID, OrderID);

-- Range Partitioning by Date
CREATE TABLE Orders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    ...
) PARTITION BY RANGE (YEAR(OrderDate)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION pFuture VALUES LESS THAN MAXVALUE
);

-- Date Dimension (Type 0 - Never Changes)
CREATE TABLE DimDate (
    DateKey INT PRIMARY KEY,
    FullDate DATE,
    DayOfWeek INT,
    DayName VARCHAR(10),
    Month INT,
    MonthName VARCHAR(10),
    Quarter INT,
    Year INT,
    IsWeekend BOOLEAN,
    IsHoliday BOOLEAN
);

-- Product Dimension (Type 2 - Track History)
CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductID INT,  -- Business Key
    ProductName VARCHAR(200),
    Category VARCHAR(100),
    Brand VARCHAR(100),
    Price DECIMAL(10,2),
    EffectiveDate DATE,
    ExpiryDate DATE,
    IsCurrent BOOLEAN
);

-- Customer Dimension (Type 1 - Overwrite)
CREATE TABLE DimCustomer (
    CustomerKey INT PRIMARY KEY,
    CustomerID INT,  -- Business Key
    CustomerName VARCHAR(100),
    CustomerSegment VARCHAR(50),
    City VARCHAR(100),
    State VARCHAR(50),
    Country VARCHAR(50)
);

-- Sales Fact Table
CREATE TABLE FactSales (
    SalesKey INT PRIMARY KEY,
    ProductKey INT,
    CustomerKey INT,
    DateKey INT,
    Quantity INT,
    UnitPrice DECIMAL(10,2),
    Revenue DECIMAL(12,2),
    Cost DECIMAL(12,2),
    Profit DECIMAL(12,2),
    FOREIGN KEY (ProductKey) REFERENCES DimProduct(ProductKey),
    FOREIGN KEY (CustomerKey) REFERENCES DimCustomer(CustomerKey),
    FOREIGN KEY (DateKey) REFERENCES DimDate(DateKey)
);

-- Update customer address - lose history
UPDATE DimCustomer 
SET Address = 'New Address', City = 'New City'
WHERE CustomerID = 12345;

-- End current record
UPDATE DimProduct 
SET ExpiryDate = '2024-01-31', IsCurrent = FALSE
WHERE ProductID = 456 AND IsCurrent = TRUE;

-- Insert new record with changes
INSERT INTO DimProduct (ProductID, ProductName, Price, EffectiveDate, IsCurrent)
VALUES (456, 'Updated Product Name', 29.99, '2024-02-01', TRUE);

ALTER TABLE DimCustomer 
ADD COLUMN PreviousSegment VARCHAR(50),
ADD COLUMN SegmentChangeDate DATE;

UPDATE DimCustomer 
SET PreviousSegment = CustomerSegment,
    CustomerSegment = 'Premium',
    SegmentChangeDate = CURRENT_DATE
WHERE CustomerID = 789;

CREATE TABLE Vehicle (
    VehicleID INT PRIMARY KEY,
    VehicleType VARCHAR(20), -- Discriminator
    Brand VARCHAR(50),
    Model VARCHAR(50),
    Year INT,
    -- Car specific
    NumberOfDoors INT,
    -- Motorcycle specific  
    HasSidecar BOOLEAN,
    -- Truck specific
    LoadCapacity DECIMAL(10,2)
);

-- Base table
CREATE TABLE Vehicle (
    VehicleID INT PRIMARY KEY,
    Brand VARCHAR(50),
    Model VARCHAR(50),
    Year INT
);

-- Derived tables
CREATE TABLE Car (
    VehicleID INT PRIMARY KEY,
    NumberOfDoors INT,
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
);

CREATE TABLE Motorcycle (
    VehicleID INT PRIMARY KEY,
    HasSidecar BOOLEAN,
    FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID)
);

CREATE TABLE Car (
    CarID INT PRIMARY KEY,
    Brand VARCHAR(50),
    Model VARCHAR(50),
    Year INT,
    NumberOfDoors INT
);

CREATE TABLE Motorcycle (
    MotorcycleID INT PRIMARY KEY,
    Brand VARCHAR(50),
    Model VARCHAR(50),
    Year INT,
    HasSidecar BOOLEAN
);

-- Base Content Entity
CREATE TABLE Content (
    ContentID INT PRIMARY KEY,
    Title VARCHAR(200),
    Author VARCHAR(100),
    CreatedDate TIMESTAMP,
    ContentType VARCHAR(50), -- Discriminator
    Status ENUM('draft', 'published', 'archived')
);

-- Article (inherits from Content)
CREATE TABLE Article (
    ContentID INT PRIMARY KEY,
    Body TEXT,
    WordCount INT,
    ReadingTime INT,
    FOREIGN KEY (ContentID) REFERENCES Content(ContentID)
);

-- Video (inherits from Content)  
CREATE TABLE Video (
    ContentID INT PRIMARY KEY,
    Duration INT, -- seconds
    Resolution VARCHAR(20),
    FileSize BIGINT,
    VideoURL VARCHAR(500),
    FOREIGN KEY (ContentID) REFERENCES Content(ContentID)
);

-- Podcast (inherits from Content)
CREATE TABLE Podcast (
    ContentID INT PRIMARY KEY,
    Duration INT, -- seconds
    EpisodeNumber INT,
    AudioURL VARCHAR(500),
    FOREIGN KEY (ContentID) REFERENCES Content(ContentID)
);

-- Content Tags (Many-to-Many)
CREATE TABLE ContentTags (
    ContentID INT,
    TagID INT,
    PRIMARY KEY (ContentID, TagID),
    FOREIGN KEY (ContentID) REFERENCES Content(ContentID),
    FOREIGN KEY (TagID) REFERENCES Tags(TagID)
);

-- IoT Sensor Measurements
CREATE TABLE sensor_measurements (
    time TIMESTAMP,
    sensor_id STRING,
    location STRING,
    measurement_type STRING, -- temperature, humidity, pressure
    value FLOAT,
    unit STRING,
    tags MAP<STRING, STRING>, -- Additional metadata
    PRIMARY KEY (time, sensor_id, measurement_type)
);

-- Sample Data
INSERT INTO sensor_measurements VALUES
('2024-01-15 10:00:00', 'sensor_001', 'warehouse_a', 'temperature', 23.5, 'celsius', {'floor': '1', 'zone': 'A'}),
('2024-01-15 10:00:00', 'sensor_001', 'warehouse_a', 'humidity', 65.2, 'percent', {'floor': '1', 'zone': 'A'}),
('2024-01-15 10:01:00', 'sensor_001', 'warehouse_a', 'temperature', 23.7, 'celsius', {'floor': '1', 'zone': 'A'});

-- Application Performance Metrics
CREATE TABLE app_metrics (
    timestamp TIMESTAMP,
    server_id STRING,
    cpu_usage_percent FLOAT,
    memory_usage_mb FLOAT,
    disk_io_read_mb FLOAT,
    disk_io_write_mb FLOAT,
    network_in_mb FLOAT,
    network_out_mb FLOAT,
    active_connections INT,
    response_time_ms FLOAT,
    error_rate_percent FLOAT,
    PRIMARY KEY (timestamp, server_id)
);

-- User Activity Events
CREATE TABLE user_events (
    event_time TIMESTAMP,
    user_id UUID,
    session_id STRING,
    event_type STRING, -- page_view, click, purchase, logout
    event_data JSON, -- Flexible event-specific data
    user_agent STRING,
    ip_address STRING,
    referrer STRING,
    PRIMARY KEY (event_time, user_id, session_id)
) PARTITION BY RANGE (event_time);

-- Monthly partitions for large datasets
CREATE TABLE metrics_2024_01 PARTITION OF metrics
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE metrics_2024_02 PARTITION OF metrics  
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Create aggregated tables for different time granularities
CREATE TABLE hourly_metrics AS
SELECT 
    DATE_TRUNC('hour', timestamp) as hour,
    server_id,
    AVG(cpu_usage_percent) as avg_cpu,
    MAX(cpu_usage_percent) as max_cpu,
    AVG(memory_usage_mb) as avg_memory,
    COUNT(*) as data_points
FROM app_metrics
GROUP BY hour, server_id;

CREATE TABLE daily_metrics AS
SELECT 
    DATE_TRUNC('day', hour) as day,
    server_id,
    AVG(avg_cpu) as avg_cpu,
    MAX(max_cpu) as peak_cpu,
    AVG(avg_memory) as avg_memory
FROM hourly_metrics
GROUP BY day, server_id;

-- Automatic data lifecycle management
DELETE FROM app_metrics 
WHERE timestamp < NOW() - INTERVAL '90 days';

DELETE FROM hourly_metrics 
WHERE hour < NOW() - INTERVAL '1 year';

-- Keep daily aggregates for 5 years
DELETE FROM daily_metrics 
WHERE day < NOW() - INTERVAL '5 years';

-- Event Store Table
CREATE TABLE event_store (
    event_id UUID PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    aggregate_type VARCHAR(100) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_version INTEGER NOT NULL,
    event_data JSONB NOT NULL,
    metadata JSONB,
    timestamp TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    correlation_id UUID,
    causation_id UUID,
    
    -- Ensure event ordering within aggregate
    UNIQUE(aggregate_id, event_version),
    
    -- Indexes for common query patterns
    INDEX idx_aggregate (aggregate_id, event_version),
    INDEX idx_event_type (event_type, timestamp),
    INDEX idx_correlation (correlation_id),
    INDEX idx_timestamp (timestamp)
);

-- Event Types for Order Aggregate
INSERT INTO event_store (event_id, aggregate_id, aggregate_type, event_type, event_version, event_data) VALUES

-- Order Placed
('evt_001', 'order_123', 'Order', 'OrderPlaced', 1, '{
  "customer_id": "cust_456", 
  "items": [{"product_id": "prod_789", "quantity": 2}],
  "total": 99.98
}'),

-- Payment Processed  
('evt_002', 'order_123', 'Order', 'PaymentProcessed', 2, '{
  "payment_method": "credit_card",
  "amount": 99.98,
  "transaction_id": "txn_abc123"
}'),

-- Order Shipped
('evt_003', 'order_123', 'Order', 'OrderShipped', 3, '{
  "tracking_number": "TRK123456789",
  "carrier": "UPS",
  "estimated_delivery": "2024-01-20"
}'),

-- Order Delivered
('evt_004', 'order_123', 'Order', 'OrderDelivered', 4, '{
  "delivered_at": "2024-01-19T14:30:00Z",
  "signature": "J.Smith"
}');

-- Order Summary Projection
CREATE TABLE order_projections (
    order_id UUID PRIMARY KEY,
    customer_id UUID,
    order_status VARCHAR(50),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP,
    shipped_at TIMESTAMP,
    delivered_at TIMESTAMP,
    last_updated TIMESTAMP
);

-- Projection Update Function
CREATE OR REPLACE FUNCTION update_order_projection()
RETURNS TRIGGER AS $$
BEGIN
    CASE NEW.event_type
        WHEN 'OrderPlaced' THEN
            INSERT INTO order_projections (
                order_id, customer_id, order_status, 
                total_amount, created_at, last_updated
            ) VALUES (
                NEW.aggregate_id,
                (NEW.event_data->>'customer_id')::UUID,
                'placed',
                (NEW.event_data->>'total')::DECIMAL,
                NEW.timestamp,
                NEW.timestamp
            );
            
        WHEN 'PaymentProcessed' THEN
            UPDATE order_projections 
            SET order_status = 'paid', last_updated = NEW.timestamp
            WHERE order_id = NEW.aggregate_id;
            
        WHEN 'OrderShipped' THEN
            UPDATE order_projections 
            SET order_status = 'shipped', 
                shipped_at = NEW.timestamp,
                last_updated = NEW.timestamp
            WHERE order_id = NEW.aggregate_id;
            
        WHEN 'OrderDelivered' THEN
            UPDATE order_projections 
            SET order_status = 'delivered',
                delivered_at = (NEW.event_data->>'delivered_at')::TIMESTAMP,
                last_updated = NEW.timestamp
            WHERE order_id = NEW.aggregate_id;
    END CASE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to maintain projections
CREATE TRIGGER order_projection_trigger
    AFTER INSERT ON event_store
    FOR EACH ROW
    EXECUTE FUNCTION update_order_projection();

-- Table Naming
customers              -- Plural nouns
order_items           -- Underscore for compound names
customer_addresses    -- Clear relationship indication

-- Column Naming  
customer_id           -- Descriptive and consistent
first_name           -- Readable format
created_timestamp    -- Include data type hint
is_active           -- Boolean prefix
total_amount        -- Clear business meaning

-- Index Naming
idx_customers_email                    -- idx_table_columns
idx_orders_customer_id_created_date   -- Composite indexes
pk_customers                          -- Primary key
fk_orders_customer_id                 -- Foreign key

-- Master Database (Write Operations)
CREATE TABLE orders_master (
    order_id UUID PRIMARY KEY,
    customer_id UUID,
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- Read Replica Configuration
-- Automatically synchronized from master
-- Used for reporting and analytics queries
-- Reduces load on master database

-- Complex aggregation query
CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_value,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

-- Refresh strategy
REFRESH MATERIALIZED VIEW customer_order_summary;

-- Horizontal partitioning by customer region
CREATE TABLE orders_us (
    LIKE orders_template INCLUDING ALL,
    CONSTRAINT orders_us_region_check CHECK (region = 'US')
);

CREATE TABLE orders_eu (
    LIKE orders_template INCLUDING ALL,
    CONSTRAINT orders_eu_region_check CHECK (region = 'EU')
);

-- Application-level routing
-- Route US customers to orders_us
-- Route EU customers to orders_eu

-- Audit table for tracking changes
CREATE TABLE customer_audit (
    audit_id SERIAL PRIMARY KEY,
    customer_id UUID NOT NULL,
    operation_type VARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    old_values JSONB,
    new_values JSONB,
    changed_by UUID,
    changed_at TIMESTAMP DEFAULT NOW(),
    change_reason TEXT
);

-- Trigger function for automatic auditing
CREATE OR REPLACE FUNCTION audit_customer_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO customer_audit (customer_id, operation_type, old_values, changed_by)
        VALUES (OLD.customer_id, 'DELETE', row_to_json(OLD), USER);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO customer_audit (customer_id, operation_type, old_values, new_values, changed_by)
        VALUES (OLD.customer_id, 'UPDATE', row_to_json(OLD), row_to_json(NEW), USER);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO customer_audit (customer_id, operation_type, new_values, changed_by)
        VALUES (NEW.customer_id, 'INSERT', row_to_json(NEW), USER);
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Add deletion tracking columns
ALTER TABLE customers 
ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE,
ADD COLUMN deleted_at TIMESTAMP,
ADD COLUMN deleted_by UUID;

-- Soft delete function
UPDATE customers 
SET is_deleted = TRUE,
    deleted_at = NOW(),
    deleted_by = :user_id
WHERE customer_id = :customer_id;

-- Views for active records only
CREATE VIEW active_customers AS
SELECT * FROM customers 
WHERE is_deleted = FALSE OR is_deleted IS NULL;

-- Add version column for concurrency control
ALTER TABLE products
ADD COLUMN version INTEGER DEFAULT 1;

-- Update with version check
UPDATE products 
SET name = :new_name,
    price = :new_price,
    version = version + 1
WHERE product_id = :product_id 
  AND version = :expected_version;

-- Application should check affected rows
-- If 0 rows affected, another user modified the record