-- Create case_hours table and Case Hour form

-- Table: case_hours
CREATE TABLE case_hours (
    id SERIAL,
    case_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    hours DECIMAL(8,2) DEFAULT 0,
    rate DECIMAL(10,2) DEFAULT 0,
    description TEXT DEFAULT NULL,
    billed_to_client BOOLEAN DEFAULT FALSE,
    approved BOOLEAN DEFAULT FALSE,
    work_type VARCHAR(50) DEFAULT NULL,
    isDeleted BOOLEAN NOT NULL DEFAULT FALSE,
    updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
CREATE INDEX case_hours_case_idx ON case_hours (case_id);
CREATE INDEX case_hours_user_idx ON case_hours (user_id);

-- Case Hour form definition
INSERT INTO forms (id, name, description, mapped_table, tenant_id, branch_id)
SELECT 38, 'Case Hour', 'Record hours worked on a case', 'case_hours', NULL, NULL
WHERE NOT EXISTS (SELECT 1 FROM forms WHERE id = 38);

INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'case_id', 'case_id', 'case_id', (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1), 1
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'case_id');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'user_id', 'user_id', 'user_id', (SELECT id FROM element_types WHERE name = 'dropdown' LIMIT 1), 2
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'user_id');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'hours', 'hours', 'hours', (SELECT id FROM element_types WHERE name = 'number' LIMIT 1), 3
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'hours');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'rate', 'rate', 'rate', (SELECT id FROM element_types WHERE name = 'number' LIMIT 1), 4
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'rate');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'description', 'description', 'description', (SELECT id FROM element_types WHERE name = 'textarea' LIMIT 1), 5
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'description');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'billed_to_client', 'billed_to_client', 'billed_to_client', (SELECT id FROM element_types WHERE name = 'checkbox' LIMIT 1), 6
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'billed_to_client');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'approved', 'approved', 'approved', (SELECT id FROM element_types WHERE name = 'checkbox' LIMIT 1), 7
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'approved');
INSERT INTO form_elements (form_id, label, field_name, data_column, type_id, order_index)
SELECT 38, 'work_type', 'work_type', 'work_type', (SELECT id FROM element_types WHERE name = 'text' LIMIT 1), 8
WHERE NOT EXISTS (SELECT 1 FROM form_elements WHERE form_id = 38 AND field_name = 'work_type');

INSERT INTO table_views (form_id, name, columns, sort_by, sort_dir)
SELECT 38, 'Default', '["case_id","user_id","hours","rate","billed_to_client","approved","work_type"]', 'updatedAt', 'DESC'
WHERE NOT EXISTS (SELECT 1 FROM table_views WHERE form_id = 38 AND name = 'Default');
