-- =============================================
-- RIDIN DATABASE SCHEMA (Production Ready)
-- Kanti Pro Solutions | $1.99/mo + taxes
-- =============================================
CREATE TABLE `users` (
    `id` VARCHAR(36) PRIMARY KEY,
    `full_name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `subscription_status` ENUM('trialing','active','canceled') DEFAULT 'trialing',
    `trial_ends_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);
CREATE TABLE `trips` (
    `id` VARCHAR(36) PRIMARY KEY,
    `user_id` VARCHAR(36) NOT NULL,
    `trip_date` DATE NOT NULL,
    `distance_km` DECIMAL(8,2) NOT NULL,
    `gross_payout` DECIMAL(8,2) NOT NULL,
    `fuel_cost` DECIMAL(8,2) DEFAULT 0,
    `maintenance_cost` DECIMAL(8,2) DEFAULT 0,
    `net_profit` DECIMAL(8,2) GENERATED ALWAYS AS (gross_payout - fuel_cost - maintenance_cost) STORED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, trip_date)
);
CREATE TABLE `expenses` (
    `id` VARCHAR(36) PRIMARY KEY,
    `user_id` VARCHAR(36) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `expense_date` DATE NOT NULL,
    `receipt_url` TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE `mileage_logs` (
    `id` VARCHAR(36) PRIMARY KEY,
    `user_id` VARCHAR(36) NOT NULL,
    `business_km` DECIMAL(8,2) NOT NULL,
    `log_date` DATE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Indexes for performance
CREATE INDEX idx_trips_user ON trips(user_id);
CREATE INDEX idx_expenses_user_date ON expenses(user_id, expense_date);
-- View for tax summaries
CREATE VIEW tax_summary AS
SELECT u.id AS user_id, SUM(t.net_profit) AS total_net_profit, SUM(t.distance_km) AS total_business_km
FROM users u LEFT JOIN trips t ON u.id = t.user_id GROUP BY u.id;