24 lines
1012 B
SQL
24 lines
1012 B
SQL
-- Migration: Add standard_rates table
|
|
-- Run this migration to add the standard_rates table for supervisor-managed default rates
|
|
|
|
-- Create standard_rates table (default rates for comparison with contractor rates)
|
|
CREATE TABLE IF NOT EXISTS standard_rates (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
sub_department_id INT,
|
|
activity VARCHAR(255),
|
|
rate DECIMAL(10, 2) NOT NULL,
|
|
effective_date DATE NOT NULL,
|
|
created_by INT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (sub_department_id) REFERENCES sub_departments(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Create indexes for standard_rates
|
|
CREATE INDEX idx_standard_rates_subdept ON standard_rates(sub_department_id);
|
|
CREATE INDEX idx_standard_rates_date ON standard_rates(effective_date);
|
|
CREATE INDEX idx_standard_rates_created_by ON standard_rates(created_by);
|
|
|
|
-- Verify table was created
|
|
SELECT 'standard_rates table created successfully' AS status;
|