-- Core Schema v1.1
CREATE TABLE IF NOT EXISTS districts (district_id INT AUTO_INCREMENT PRIMARY KEY, district_name VARCHAR(100) NOT NULL, district_description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS branches (branch_id INT AUTO_INCREMENT PRIMARY KEY, district_id INT NULL, branch_name VARCHAR(150) NOT NULL, branch_number INT NULL, phone VARCHAR(30), fax VARCHAR(30), email VARCHAR(150),
  physical_address1 VARCHAR(120), physical_address2 VARCHAR(120), physical_city VARCHAR(80), physical_state VARCHAR(80), physical_postal VARCHAR(20), physical_country VARCHAR(80) DEFAULT 'USA',
  po_address1 VARCHAR(120), po_address2 VARCHAR(120), po_city VARCHAR(80), po_state VARCHAR(80), po_postal VARCHAR(20), po_country VARCHAR(80) DEFAULT 'USA',
  has_prep_room TINYINT(1) DEFAULT 0, has_refrigeration TINYINT(1) DEFAULT 0, arrangement_rooms INT DEFAULT 0, chapels INT DEFAULT 0, state_rooms INT DEFAULT 0, selection_room TINYINT(1) DEFAULT 0,
  esign_expire_hours INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (district_id) REFERENCES districts(district_id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS branch_rooms (room_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NOT NULL, room_name VARCHAR(120) NOT NULL, room_type ENUM('prep','refrigeration','stateroom','parlor','chapel','selection','arrangement','other') DEFAULT 'other', is_body_location TINYINT(1) DEFAULT 1, display_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admin_users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(60) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','district_manager','manager','staff') DEFAULT 'staff', active TINYINT(1) DEFAULT 1, branch_id INT NULL, district_id INT NULL, email VARCHAR(150), phone VARCHAR(30), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE SET NULL, FOREIGN KEY (district_id) REFERENCES districts(district_id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_branch_access (access_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, branch_id INT NOT NULL, access_level ENUM('view','edit','manage') DEFAULT 'view', UNIQUE KEY uniq_user_branch (user_id, branch_id), FOREIGN KEY (user_id) REFERENCES admin_users(user_id) ON DELETE CASCADE, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS persons (person_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NULL, first_name VARCHAR(80) NOT NULL, middle_name VARCHAR(80), last_name VARCHAR(80) NOT NULL, suffix VARCHAR(20), preferred_name VARCHAR(80), is_deceased ENUM('Y','N') DEFAULT 'N', date_of_birth DATE, place_of_birth_city VARCHAR(80), place_of_birth_state VARCHAR(80), place_of_birth_country VARCHAR(80) DEFAULT 'USA', date_of_death DATE, place_of_death_city VARCHAR(80), place_of_death_state VARCHAR(80), place_of_death_country VARCHAR(80) DEFAULT 'USA', contact_phone VARCHAR(30), contact_email VARCHAR(150), address1 VARCHAR(120), address2 VARCHAR(120), city VARCHAR(80), state VARCHAR(80), postal VARCHAR(20), country VARCHAR(80) DEFAULT 'USA', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS services (service_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NOT NULL, person_id INT NOT NULL, service_type ENUM('Visitation','Viewing','Funeral','Service','Memorial','Graveside','Cremation','Other') DEFAULT 'Other', service_status ENUM('Scheduled','Active','Completed','Cancelled') DEFAULT 'Scheduled', service_date DATE, service_time TIME, location VARCHAR(200), cremation_flag TINYINT(1) DEFAULT 0, cemetery VARCHAR(200), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE, FOREIGN KEY (person_id) REFERENCES persons(person_id) ON DELETE CASCADE, INDEX (branch_id, service_date), INDEX (person_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS disposition_status_lookup (code VARCHAR(40) PRIMARY KEY, label VARCHAR(120) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO disposition_status_lookup (code,label) VALUES
 ('first_call','Place of death / first call'),('removal','Removal / transport to FH'),('prep_room','In prep room'),('refrigeration','In refrigeration'),
 ('stateroom','In state room / parlor'),('chapel','In chapel'),('church','At church'),('crematory','At crematory'),('cremation_in_progress','Cremation in progress'),
 ('cremains_ready_crematory','Cremated remains ready at crematory'),('cremains_ready_pickup','Cremated remains ready for pickup'),('cremains_ready_ship','Cremated remains ready to ship'),
 ('cremains_ready_interment','Cremated remains ready for interment'),('cremains_picked_up','Cremated remains picked up'),('cremains_buried','Cremated remains buried'),
 ('transferred_branch','Body transferred to another branch'),('released_other_firm','Body released to another firm'),('interred_entombed','Body interred/entombed'),('inurned','Cremated remains inurned');
CREATE TABLE IF NOT EXISTS disposition_status (disp_id INT AUTO_INCREMENT PRIMARY KEY, service_id INT NOT NULL, branch_id INT NOT NULL, status_code VARCHAR(40) NOT NULL, room_id INT NULL, effective_at DATETIME DEFAULT CURRENT_TIMESTAMP, notes TEXT, FOREIGN KEY (service_id) REFERENCES services(service_id) ON DELETE CASCADE, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE, FOREIGN KEY (status_code) REFERENCES disposition_status_lookup(code) ON DELETE RESTRICT, FOREIGN KEY (room_id) REFERENCES branch_rooms(room_id) ON DELETE SET NULL, INDEX (service_id, effective_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS case_history (history_id INT AUTO_INCREMENT PRIMARY KEY, service_id INT NOT NULL, branch_id INT NOT NULL, user_id INT NULL, note TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (service_id) REFERENCES services(service_id) ON DELETE CASCADE, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES admin_users(user_id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tasks (task_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NOT NULL, service_id INT NULL, title VARCHAR(200) NOT NULL, status ENUM('Open','In Progress','Done','Canceled') DEFAULT 'Open', due_at DATETIME NULL, assigned_to INT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (branch_id) REFERENCES branches(branch_id) ON DELETE CASCADE, FOREIGN KEY (service_id) REFERENCES services(service_id) ON DELETE SET NULL, FOREIGN KEY (assigned_to) REFERENCES admin_users(user_id) ON DELETE SET NULL, FOREIGN KEY (created_by) REFERENCES admin_users(user_id) ON DELETE SET NULL, INDEX (branch_id, status, due_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS system_activity (activity_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(60), action_type VARCHAR(80), target VARCHAR(120), description TEXT, ip_address VARCHAR(45), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX (username), INDEX (action_type), INDEX (created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO districts (district_id, district_name) VALUES (1, 'North Arkansas');
INSERT IGNORE INTO branches (branch_id, district_id, branch_name, branch_number, phone, email, has_prep_room, has_refrigeration, arrangement_rooms, chapels, state_rooms, selection_room) VALUES (29, 1, 'Roller-Christeson FH', 29, '(000)000-0000', 'info@example.com', 1, 1, 2, 1, 3, 1);
