/* activity_log */

CREATE TABLE activity_log (
  id SERIAL PRIMARY KEY,
  ip_address VARCHAR(255),
  activity TEXT,
  reference INT,
  user_id INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL
);

-- For the updated_at timestamp to automatically update on row modification, 
-- you need to create a trigger in PostgreSQL as follows:

-- Step 1: Create a function to update the timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- Step 2: Create a trigger that uses this function
CREATE TRIGGER update_activity_log_updated_at
BEFORE UPDATE ON activity_log
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();


/* master_floor */

CREATE TABLE master_floor (
  id SERIAL PRIMARY KEY,
  floor VARCHAR(20),
  floor_image VARCHAR(255),
  status INT DEFAULT 1,
  enable_donation INT NOT NULL DEFAULT 0,
  last_updated_by INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  flag SMALLINT NOT NULL DEFAULT 1
);

-- For the updated_at column to auto-update on modification, use a trigger in PostgreSQL:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_master_floor_updated_at
BEFORE UPDATE ON master_floor
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

TRUNCATE TABLE master_floor;
--
-- Dumping data for table master_floor
--

INSERT INTO master_floor (id, floor, floor_image, "status", enable_donation, last_updated_by, created_at, updated_at, deleted_at, flag) VALUES
(2, 'Floor 1', 'simple', 1, 1, 1, '2023-12-22 07:42:45', '2024-02-11 15:02:32', NULL, 1);

/* master_layout */

CREATE TABLE master_layout (
  id SERIAL PRIMARY KEY,
  id_floor INT,
  id_row INT,
  "no" DECIMAL(10,0),
  code VARCHAR(20),
  seat_code VARCHAR(3) NOT NULL,
  coords VARCHAR(255),
  shape VARCHAR(50),
  price NUMERIC(17,2),
  status INT DEFAULT 1,
  last_updated_by INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  flag SMALLINT NOT NULL DEFAULT 1
);

-- To handle automatic updates to the 'updated_at' column, create a trigger:
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_master_layout_updated_at
BEFORE UPDATE ON master_layout
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

TRUNCATE TABLE master_layout;
--
-- Dumping data for table master_layout
--

INSERT INTO master_layout (id, id_floor, id_row, no, code, seat_code, coords, shape, price, status, last_updated_by, created_at, updated_at, deleted_at, flag) VALUES
(1, 2, 6, '1', '2-6-1', 'A1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(2, 2, 6, '2', '2-6-2', 'A2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(3, 2, 6, '3', '2-6-3', 'A3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(4, 2, 6, '5', '2-6-5', 'A5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(5, 2, 6, '4', '2-6-4', 'A4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(6, 2, 6, '6', '2-6-6', 'A6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(7, 2, 6, '7', '2-6-7', 'A7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(8, 2, 6, '8', '2-6-8', 'A8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(9, 2, 7, '1', '2-7-1', 'B1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(10, 2, 7, '2', '2-7-2', 'B2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(11, 2, 7, '3', '2-7-3', 'B3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(12, 2, 7, '4', '2-7-4', 'B4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(13, 2, 7, '5', '2-7-5', 'B5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(14, 2, 7, '6', '2-7-6', 'B6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(15, 2, 7, '7', '2-7-7', 'B7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(16, 2, 7, '8', '2-7-8', 'B8', NULL, 'circle', 30.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-22 11:10:18', NULL, 1),
(17, 2, 8, '1', '2-8-1', 'C1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:33', NULL, 1),
(18, 2, 8, '2', '2-8-2', 'C2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(19, 2, 8, '3', '2-8-3', 'C3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:39', NULL, 1),
(20, 2, 8, '4', '2-8-4', 'C4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:42', NULL, 1),
(21, 2, 8, '5', '2-8-5', 'C5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:45', NULL, 1),
(22, 2, 8, '6', '2-8-6', 'C6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:48', NULL, 1),
(23, 2, 8, '7', '2-8-7', 'C7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:51', NULL, 1),
(24, 2, 8, '8', '2-8-8', 'C8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:22:55', NULL, 1),
(25, 2, 9, '8', '2-9-8', 'D8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:23:23', NULL, 1),
(26, 2, 9, '7', '2-9-7', 'D7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(27, 2, 9, '1', '2-9-1', 'D1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:23:38', NULL, 1),
(28, 2, 9, '2', '2-9-2', 'D2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(29, 2, 9, '3', '2-9-3', 'D3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(30, 2, 9, '4', '2-9-4', 'D4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(31, 2, 9, '5', '2-9-5', 'D5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:23:51', NULL, 1),
(32, 2, 9, '6', '2-9-6', 'D6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:23:57', NULL, 1),
(33, 2, 10, '1', '2-10-1', 'E1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:00', NULL, 1),
(34, 2, 10, '2', '2-10-2', 'E2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(35, 2, 10, '3', '2-10-3', 'E3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:06', NULL, 1),
(36, 2, 10, '4', '2-10-4', 'E4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:09', NULL, 1),
(37, 2, 10, '5', '2-10-5', 'E5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:12', NULL, 1),
(38, 2, 10, '6', '2-10-6', 'E6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:15', NULL, 1),
(39, 2, 10, '7', '2-10-7', 'E7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:17', NULL, 1),
(40, 2, 10, '8', '2-10-8', 'E8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:20', NULL, 1),
(41, 2, 10, '9', '2-10-9', 'E9', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:24:23', NULL, 1),
(42, 2, 10, '10', '2-10-10', 'E10', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:15', NULL, 1),
(43, 2, 11, '1', '2-11-1', 'F1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(44, 2, 11, '2', '2-11-2', 'F2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:24', NULL, 1),
(45, 2, 11, '3', '2-11-3', 'F3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:25', NULL, 1),
(46, 2, 11, '4', '2-11-4', 'F4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:28', NULL, 1),
(47, 2, 11, '5', '2-11-5', 'F5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-07 04:14:33', NULL, 1),
(48, 2, 11, '6', '2-11-6', 'F6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:32', NULL, 1),
(49, 2, 11, '7', '2-11-7', 'F7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(50, 2, 11, '8', '2-11-8', 'F8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:38', NULL, 1),
(51, 2, 11, '9', '2-11-9', 'F9', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:44', NULL, 1),
(52, 2, 11, '10', '2-11-10', 'F10', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:48', NULL, 1),
(53, 2, 12, '1', '2-12-1', 'G1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:54', NULL, 1),
(54, 2, 12, '2', '2-12-2', 'G2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:56', NULL, 1),
(55, 2, 12, '3', '2-12-3', 'G3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:25:58', NULL, 1),
(56, 2, 12, '4', '2-12-4', 'G4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:01', NULL, 1),
(57, 2, 12, '5', '2-12-5', 'G5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(58, 2, 12, '6', '2-12-6', 'G6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:06', NULL, 1),
(59, 2, 12, '7', '2-12-7', 'G7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-03-02 16:04:02', NULL, 1),
(60, 2, 12, '8', '2-12-8', 'G8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:11', NULL, 1),
(61, 2, 12, '9', '2-12-9', 'G9', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:13', NULL, 1),
(62, 2, 12, '10', '2-12-10', 'G10', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:17', NULL, 1),
(63, 2, 13, '1', '2-13-1', 'H1', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:20', NULL, 1),
(64, 2, 13, '2', '2-13-2', 'H2', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:39', NULL, 1),
(65, 2, 13, '3', '2-13-3', 'H3', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:41', NULL, 1),
(66, 2, 13, '4', '2-13-4', 'H4', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:42', NULL, 1),
(67, 2, 13, '5', '2-13-5', 'H5', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:44', NULL, 1),
(68, 2, 13, '6', '2-13-6', 'H6', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:46', NULL, 1),
(69, 2, 13, '7', '2-13-7', 'H7', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:49', NULL, 1),
(70, 2, 13, '8', '2-13-8', 'H8', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:51', NULL, 1),
(71, 2, 13, '9', '2-13-9', 'H9', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:53', NULL, 1),
(72, 2, 13, '10', '2-13-10', 'H10', NULL, 'circle', 25.00, 1, NULL, '2024-01-16 18:38:19', '2024-02-19 23:26:57', NULL, 1);

/* master_row */

CREATE TABLE master_row (
  id SERIAL PRIMARY KEY,
  rows VARCHAR(20),
  status INT DEFAULT 1,
  last_updated_by INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL,
  flag SMALLINT NOT NULL DEFAULT 1
);

-- To automatically update the 'updated_at' timestamp:
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_master_row_updated_at
BEFORE UPDATE ON master_row
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();


TRUNCATE TABLE master_row;
--
-- Dumping data for table master_row
--

INSERT INTO master_row (id, rows, status, last_updated_by, created_at, updated_at, deleted_at, flag) VALUES
(6, 'A', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(7, 'B', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(8, 'C', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(9, 'D', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(10, 'E', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(11, 'F', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(12, 'G', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(13, 'H', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(14, 'I', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(15, 'J', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(16, 'K', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(17, 'L', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(18, 'M', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(19, 'N', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(20, 'O', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(21, 'P', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(22, 'Q', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(23, 'R', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(24, 'S', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(25, 'T', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(26, 'U', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(27, 'V', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(28, 'W', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(29, 'X', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(30, 'Y', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1),
(31, 'Z', 1, 1, '2023-12-22 07:50:36', '2023-12-22 07:50:36', NULL, 1);

/* transaction_order_detail */

CREATE TABLE transaction_order_detail (
  id SERIAL PRIMARY KEY,
  id_header INT,
  id_layout INT,
  price NUMERIC(17,2),
  ticket_name VARCHAR(250),
  ticket_message TEXT,
  status INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL
);

-- Trigger to automatically update 'updated_at' upon row modification
CREATE TRIGGER update_transaction_order_detail_updated_at
BEFORE UPDATE ON transaction_order_detail
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

/* transaction_order_header */
-- Dropping the table if it already exists

-- Creating the table
CREATE TABLE transaction_order_header (
  id SERIAL PRIMARY KEY,
  id_payment_plan INT,
  payment_method VARCHAR(30) NOT NULL,
  order_number VARCHAR(30),
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  email VARCHAR(320),
  phone VARCHAR(15),
  total_unit INT,
  donation_amount NUMERIC(17,2) NOT NULL DEFAULT 0.00,
  total_price NUMERIC(17,2),
  gateway_fee NUMERIC (17,2) NOT NULL DEFAULT 0.00,
  price_due NUMERIC(17,2), -- Note: The comment from MySQL is removed as PostgreSQL column comments are set differently
  street_address VARCHAR(255),
  suburb VARCHAR(255),
  state VARCHAR(52) NOT NULL,
  postcode VARCHAR(15),
  country VARCHAR(52),
  order_type VARCHAR(30) NOT NULL,
  status INT DEFAULT 1,
  debtor_acc varchar(100) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL
);

-- Adding a comment to the 'price_due' column post-creation
COMMENT ON COLUMN transaction_order_header.price_due IS 'transaction_order_header.total_price / payment_plan.duration';


-- Trigger to automatically update 'updated_at' upon row modification
CREATE TRIGGER update_transaction_order_header_updated_at
BEFORE UPDATE ON transaction_order_header
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

/* transaction_payment_plan */

CREATE TABLE transaction_payment_plan (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  description TEXT,
  duration INT, -- Note: The comment from MySQL is not included directly in the CREATE statement.
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL
);

-- Trigger for the transaction_payment_plan table to automatically update 'updated_at'
CREATE TRIGGER update_transaction_payment_plan_updated_at
BEFORE UPDATE ON transaction_payment_plan
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

-- Adding a comment to the 'duration' column post-creation
COMMENT ON COLUMN transaction_payment_plan.duration IS 'How many payments are required. Recurring payments are handled by clients';

TRUNCATE TABLE transaction_payment_plan;
--
-- Dumping data for table transaction_payment_plan
--

INSERT INTO transaction_payment_plan (id, title, description, duration, created_at, updated_at, deleted_at) VALUES
(1, '1 full payment of {{amount}}', 'Pay in full at checkout', 1, '2024-01-11 14:10:45', '2024-01-11 14:10:45', NULL),
(2, '2 payments of {{amount}}', 'Split your donation into 2 equal payments over a maximum of 2 years. The first installment to be paid at checkout.', 2, '2024-01-11 14:10:45', '2024-01-11 14:10:45', NULL),
(3, '4 payments of {{amount}}', 'Split your donation into 4 equal payments over a maximum of 2 years. The first installments to be paid at checkout.', 4, '2024-01-11 14:10:45', '2024-01-11 14:10:45', NULL),
(4, '20 payments of {{amount}}', 'Split your donation into monthly payments over 20 months. The first installment to be paid at checkout.', 20, '2024-01-11 14:10:45', '2024-01-11 14:10:45', NULL);


CREATE TABLE web (
  id SERIAL PRIMARY KEY,
  home_title VARCHAR(255),
  home_description_1 TEXT, -- Comment: 'Content of homepage description'
  home_description_2 TEXT, -- Comment: 'Content of homepage description'
  home_banner_image VARCHAR(255),
  about_video VARCHAR(255),
  about_image VARCHAR(255),
  about_text TEXT,
  privacy_policy TEXT,
  terms TEXT,
  background_color VARCHAR(255),
  text_color VARCHAR(255),
  button_color VARCHAR(255),
  status VARCHAR(255),
  template INT DEFAULT 1, -- Comment: 'Homepage template options'
  meta_title VARCHAR(255),
  meta_description TEXT,
  meta_image VARCHAR(255),
  favicon VARCHAR(255),
  ga_code VARCHAR(255),
  last_updated_by INT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL
);

-- Comments for specific columns
COMMENT ON COLUMN web.home_description_1 IS 'Content of homepage description';
COMMENT ON COLUMN web.home_description_2 IS 'Content of homepage description';
COMMENT ON COLUMN web.template IS 'Homepage template options';

-- Trigger to automatically update 'updated_at' upon row modification
CREATE TRIGGER update_web_updated_at
BEFORE UPDATE ON web
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

TRUNCATE TABLE web;
--
-- Dumping data for table web
--

INSERT INTO web (id, home_title, home_description_1, home_description_2, home_banner_image, about_video, about_image, about_text, privacy_policy, terms, background_color, text_color, button_color, status, template, meta_title, meta_description, meta_image, favicon, ga_code, last_updated_by, created_at, updated_at, deleted_at) VALUES
(1, 'Musical Chairs', '<p>The ACO at Pier 2/3 &ndash; Building our future together</p>', '<p>We invite you to join us by dedicating a seat in the ACO&rsquo;s new home. Your contribution to our future will be acknowledged with an engraved plaque on the back of your chosen Musical Chair.</p>', 'https://b313e8803f7a4150a884-6e0b076a1e92e31c40be44f466689c50.ssl.cf5.rackcdn.com/images/7943/home-bg_1701778786.jpg', 'https://www.youtube.com/embed/5ZDb-hOh4Tc?si=LWWNG56w3m1TZw3b', '', '<p><strong>Lorem Ipsum</strong>&nbsp;is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry&#39;s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.</p>  <p>It is a long-established fact that a reader will be distracted by the readable content of a page when looking at its layout. The point of using Lorem Ipsum is that it has a more-or-less normal distribution of letters, as opposed to using &#39;Content here, content here&#39;, making it look like readable English. Many desktop publishing packages and web page editors now use Lorem Ipsum as their default model text, and a search for &#39;lorem ipsum&#39; will uncover many web sites still in their infancy. Various versions have evolved over the years, sometimes by accident, sometimes on purpose (injected humour and the like).</p>', NULL, NULL, '#000000', '#ffffff', '#F95D62', '', 1, '', '', '', '', '', NULL, '2023-12-02 01:45:56', '2023-12-02 01:45:56', NULL);


BEGIN;

-- CREATE TABLE "auth_rule" ------------------------------------
CREATE TABLE "public"."auth_rule" ( 
	"name" Character Varying( 64 ) NOT NULL,
	"data" Bytea,
	"created_at" Integer,
	"updated_at" Integer,
	PRIMARY KEY ( "name" ) );
 -- -------------------------------------------------------------;

COMMIT;


BEGIN;

-- CREATE TABLE "auth_item" ------------------------------------
CREATE TABLE "public"."auth_item" ( 
	"name" Character Varying( 64 ) NOT NULL,
	"type" SmallInt NOT NULL,
	"description" Text,
	"rule_name" Character Varying( 64 ),
	"data" Bytea,
	"created_at" Integer,
	"updated_at" Integer,
	PRIMARY KEY ( "name" ) );
 -- -------------------------------------------------------------;

-- CREATE INDEX "idx-auth_item-type" ---------------------------
CREATE INDEX "idx-auth_item-type" ON "public"."auth_item" USING btree( "type" Asc NULLS Last );
-- -------------------------------------------------------------

-- CREATE INDEX "rule_name" ------------------------------------
CREATE INDEX "rule_name" ON "public"."auth_item" USING btree( "rule_name" Asc NULLS Last );
-- -------------------------------------------------------------

COMMIT;

INSERT INTO auth_item (name, type, created_at, updated_at)
VALUES ('ADMIN', 1, extract(epoch from current_timestamp), extract(epoch from current_timestamp)),
       ('SUPER_ADMIN', 1, extract(epoch from current_timestamp), extract(epoch from current_timestamp)),
       ('MAPPER', 1, extract(epoch from current_timestamp), extract(epoch from current_timestamp));


BEGIN;

-- CREATE LINK "auth_item_ibfk_1" ------------------------------
ALTER TABLE "public"."auth_item"
	ADD CONSTRAINT "auth_item_ibfk_1" FOREIGN KEY ( "rule_name" )
	REFERENCES "public"."auth_rule" ( "name" ) MATCH SIMPLE
	ON DELETE No Action
	ON UPDATE No Action;-- -------------------------------------------------------------;

COMMIT;

BEGIN;

-- CREATE TABLE "auth_item_child" ------------------------------
CREATE TABLE "public"."auth_item_child" ( 
	"parent" Character Varying( 64 ) NOT NULL,
	"child" Character Varying( 64 ) NOT NULL,
	PRIMARY KEY ( "parent", "child" ) );
 -- -------------------------------------------------------------;

-- CREATE INDEX "child" ----------------------------------------
CREATE INDEX "child" ON "public"."auth_item_child" USING btree( "child" Asc NULLS Last );
-- -------------------------------------------------------------

COMMIT;

BEGIN;

-- CREATE LINK "auth_item_child_ibfk_1" ------------------------
ALTER TABLE "public"."auth_item_child"
	ADD CONSTRAINT "auth_item_child_ibfk_1" FOREIGN KEY ( "parent" )
	REFERENCES "public"."auth_item" ( "name" ) MATCH SIMPLE
	ON DELETE No Action
	ON UPDATE No Action;-- -------------------------------------------------------------;

COMMIT;

BEGIN;

-- CREATE LINK "auth_item_child_ibfk_2" ------------------------
ALTER TABLE "public"."auth_item_child"
	ADD CONSTRAINT "auth_item_child_ibfk_2" FOREIGN KEY ( "child" )
	REFERENCES "public"."auth_item" ( "name" ) MATCH SIMPLE
	ON DELETE No Action
	ON UPDATE No Action;-- -------------------------------------------------------------;

COMMIT;

INSERT INTO auth_item_child(parent, child)
VALUES ('SUPER_ADMIN', 'ADMIN')
,('SUPER_ADMIN', 'MAPPER');

BEGIN;

-- CREATE TABLE "auth_assignment" ------------------------------
CREATE TABLE "public"."auth_assignment" ( 
	"item_name" Character Varying( 64 ) NOT NULL,
	"user_id" Character Varying( 64 ) NOT NULL,
	"created_at" Integer,
	PRIMARY KEY ( "item_name", "user_id" ) );
 -- -------------------------------------------------------------;

-- CREATE INDEX "auth_assignment_user_id_idx" ------------------
CREATE INDEX "auth_assignment_user_id_idx" ON "public"."auth_assignment" USING btree( "user_id" Asc NULLS Last );
-- -------------------------------------------------------------


INSERT INTO auth_assignment(item_name, user_id, created_at)
VALUES ('ADMIN', 1, extract(epoch from current_timestamp)),
('SUPER_ADMIN', 2, extract(epoch from current_timestamp));
COMMIT;

BEGIN;

-- CREATE LINK "auth_assignment_ibfk_1" ------------------------
ALTER TABLE "public"."auth_assignment"
	ADD CONSTRAINT "auth_assignment_ibfk_1" FOREIGN KEY ( "item_name" )
	REFERENCES "public"."auth_item" ( "name" ) MATCH SIMPLE
	ON DELETE No Action
	ON UPDATE No Action;-- -------------------------------------------------------------;

COMMIT;


-- For activity_log
ALTER TABLE activity_log ADD PRIMARY KEY (id);
CREATE INDEX idx_activity_log_user_id ON activity_log (user_id);

-- For master_floor
ALTER TABLE master_floor ADD PRIMARY KEY (id);
CREATE INDEX idx_master_floor_last_updated_by ON master_floor (last_updated_by);

-- For master_layout
ALTER TABLE master_layout ADD PRIMARY KEY (id);
CREATE INDEX idx_master_layout_id_floor ON master_layout (id_floor);
CREATE INDEX idx_master_layout_id_row ON master_layout (id_row);
CREATE INDEX idx_master_layout_last_updated_by ON master_layout (last_updated_by);

-- For master_row
ALTER TABLE master_row ADD PRIMARY KEY (id);
CREATE INDEX idx_master_row_last_updated_by ON master_row (last_updated_by);

-- For transaction_order_detail
ALTER TABLE transaction_order_detail ADD PRIMARY KEY (id);
CREATE INDEX idx_transaction_order_detail_id_header ON transaction_order_detail (id_header);
CREATE INDEX idx_transaction_order_detail_id_layout ON transaction_order_detail (id_layout);

-- For transaction_order_header
ALTER TABLE transaction_order_header ADD PRIMARY KEY (id);
CREATE INDEX idx_transaction_order_header_id_payment_plan ON transaction_order_header (id_payment_plan);

-- For transaction_payment_plan
ALTER TABLE transaction_payment_plan ADD PRIMARY KEY (id);

-- For web
ALTER TABLE web ADD PRIMARY KEY (id);
CREATE INDEX idx_web_last_updated_by ON web (last_updated_by);

-- Constraints for table activity_log
ALTER TABLE activity_log
  ADD CONSTRAINT activity_log_ibfk_1 FOREIGN KEY (user_id) REFERENCES master_user (id),
  ADD CONSTRAINT activity_log_ibfk_2 FOREIGN KEY (user_id) REFERENCES master_user (id),
  ADD CONSTRAINT activity_log_ibfk_3 FOREIGN KEY (user_id) REFERENCES master_user (id),
  ADD CONSTRAINT activity_log_ibfk_4 FOREIGN KEY (user_id) REFERENCES master_user (id);

-- Constraints for table master_floor
ALTER TABLE master_floor
  ADD CONSTRAINT master_floor_ibfk_1 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_floor_ibfk_2 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_floor_ibfk_3 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_floor_ibfk_4 FOREIGN KEY (last_updated_by) REFERENCES master_user (id);

-- Constraints for table master_layout
ALTER TABLE master_layout
  ADD CONSTRAINT master_layout_ibfk_1 FOREIGN KEY (id_floor) REFERENCES master_floor (id),
  ADD CONSTRAINT master_layout_ibfk_10 FOREIGN KEY (id_floor) REFERENCES master_floor (id),
  ADD CONSTRAINT master_layout_ibfk_11 FOREIGN KEY (id_row) REFERENCES master_row (id),
  ADD CONSTRAINT master_layout_ibfk_12 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_layout_ibfk_2 FOREIGN KEY (id_row) REFERENCES master_row (id),
  ADD CONSTRAINT master_layout_ibfk_3 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_layout_ibfk_4 FOREIGN KEY (id_floor) REFERENCES master_floor (id),
  ADD CONSTRAINT master_layout_ibfk_5 FOREIGN KEY (id_row) REFERENCES master_row (id),
  ADD CONSTRAINT master_layout_ibfk_6 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_layout_ibfk_7 FOREIGN KEY (id_floor) REFERENCES master_floor (id),
  ADD CONSTRAINT master_layout_ibfk_8 FOREIGN KEY (id_row) REFERENCES master_row (id),
  ADD CONSTRAINT master_layout_ibfk_9 FOREIGN KEY (last_updated_by) REFERENCES master_user (id);

-- Constraints for table master_row
ALTER TABLE master_row
  ADD CONSTRAINT master_row_ibfk_1 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_row_ibfk_2 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_row_ibfk_3 FOREIGN KEY (last_updated_by) REFERENCES master_user (id),
  ADD CONSTRAINT master_row_ibfk_4 FOREIGN KEY (last_updated_by) REFERENCES master_user (id);

-- Constraints for table transaction_order_detail
ALTER TABLE transaction_order_detail
  ADD CONSTRAINT transaction_order_detail_ibfk_1 FOREIGN KEY (id_header) REFERENCES transaction_order_header (id),
  ADD CONSTRAINT transaction_order_detail_ibfk_2 FOREIGN KEY (id_layout) REFERENCES master_layout (id),
  ADD CONSTRAINT transaction_order_detail_ibfk_3 FOREIGN KEY (id_header) REFERENCES transaction_order_header (id),
  ADD CONSTRAINT transaction_order_detail_ibfk_4 FOREIGN KEY (id_layout) REFERENCES master_layout (id),
  ADD CONSTRAINT transaction_order_detail_ibfk_5 FOREIGN KEY (id_header) REFERENCES transaction_order_header (id),
  ADD CONSTRAINT transaction_order_detail_ibfk_6 FOREIGN KEY (id_layout) REFERENCES master_layout (id);

-- Constraints for table transaction_order_header
ALTER TABLE transaction_order_header
  ADD CONSTRAINT transaction_order_header_ibfk_1 FOREIGN KEY (id_payment_plan) REFERENCES transaction_payment_plan (id),
  ADD CONSTRAINT transaction_order_header_ibfk_2 FOREIGN KEY (id_payment_plan) REFERENCES transaction_payment_plan (id),
  ADD CONSTRAINT transaction_order_header_ibfk_3 FOREIGN KEY (id_payment_plan) REFERENCES transaction_payment_plan (id);

-- Constraints for table web
ALTER TABLE web
  ADD CONSTRAINT web_ibfk_1 FOREIGN KEY (last_updated_by) REFERENCES master_user (id);
