#!/usr/bin/perl -w
# Make tables for the database from .VIMSS file and the curated setup table
# Expects $dir to contain exptid.VIMSS files and puts tables to upload to the database there:
# 	ChipInfo,SpotInfo,SpotPos,SpotAttr,ExpInfo,SubExpInfo,SubExpAttr,ArrayData,ArrayDataAttr
# SetupTable should be hand-modified from the output of SMDSetupTable.pl, or see detailed spec below
# Outputs SQL commands to be used to upload the database (I recommend mysql -B)
#
# Please note the terms "experiment" and "sub-experiment", where a
# sub-experiment is a single array hybridization and an experiment
# is a collection of replicate sub-experiments, do not
# correspond to SMD's experiment-set (an arbitrary bag of what we
# call sub-experiments) or experiment (what we call sub-experiment).
#
# The setup table should be a tab-delimited table, with
# null fields represented with the empty string, no quoting,
# a header line, and one line per sub-experiment, with the following
# columns (in any order). Please note that any additional
# columns will be silently ignored.

#    Per-chip fields (even though they are present for each line)
#	ChipUniq - the experiments with the same value for this attribute are based on the same chip
#	ChipType - CDNA or cDNA for 2-color cDNA microarrays
#	ChipDescription - put into the database; optional
#       ChipSource - where the chip is from - ideally the earliest publication using the chip & describing its design
#    Per-experiment fields (even though they are present for each line):
#	GroupUniq - the group of sub-experiments with the same value for this field form an experiment
#	Citation - optional
#	PubMedID - optional
#	Condition - curated, e.g. "Heat" or "Control"
#	ControlInfo - curated, e.g. "H2O2 48uM t0"
#	TreatmentInfo - curated, e.g. "H2O2 48uM t10"
#	Comment - curated
#    Per-sub-experiment fields
#	Biological - use "?" if you don't know the replicate structure
#	Technical - use "?" if you want the loader to number the technical replicates for you
#	TreatmentChannel - "RED" (or "CY5" or "2") is default; use "Green" (or "CY3" or "1") for dye-swaps
#	SMDExptid - a somewhat misleading field name - SMDLoader looks in
#                   directory/SMDexptid{Suffix} for the actual data
#    Per-sub-experiment fields in the SMD-derived setup tables
#    (these are for the curator -- they are NOT read by the loader and are NOT required)
#	Experiment Name
#	Description
#	ChipName
#	Channel	1 Description
#	Channel 2 Description
#
# The file for each sub-experiment should be a tab-delimited file
# with one line per spot, a header line, and optional leading attributes
# in the format !Attribute=Value (one per line). These attributes
# are saved into the SubExpAttrs table. Optionally, these
# attributes are loaded into the database (per experiment, not
# per sub-experiment):
#	Experimenter - who to contact regarding this sub-experiment
#	Contact email - his or her email address
#
# The columns for each spot are put into the array data attributes table,
# but there are some required fields (for ArrayData):
#   some are actually per-spot not per-subexperiment:
#	SPOT - a unique name or id for the spot on this chip
#       NAME - a descriptive name about where the spot came from (will use SOURCE or SPOT if missing)
#       VIMSS - the VIMSS id for the corresponding gene, or empty
#       GENE - if we don't actually have the spot data, include a field named GENE and the loader will make do with that
# and some recommended attributes (per-spot):
#	SECTOR - which sector the spot is located in
#	SECTORROW - within-sector location
#	SECTORCOL - within-sector location
#	PROW - where it was printed from
#	PCOL - where it was printed from
#	FAILED - this is 0 if the spot printed OK
#	IS_VERIFIED - this is "N" or "0" if the spot was not verified
#	IS_CONTAMINATED - this is "Y" or 1 if there is evidence of contamination, or
#                         0 or "N" if it is not contaminated or "U" for unknown
# and more recommended per-spot per-subexperiment attributes:
#	CH1D_MEAN - mean foreground intensity (total - background) for Channel 1
#	CH2D_MEAN - mean foreground intensity (total - background) for Channel 2
#	LOG_RAT2N_MEAN - normalized log2(channel2/channel1)
#	LOG2RATIO - log2(channel2/channel1) - will be computed if CH1D_MEAN,CH2D_MEAN are present
#       CORR - correlation between foreground and background intensities within this spot
#	LEFT - pixel position boundary of the spot in this scan
#	RIGHT - pixel position boundary of the spot in this scan
#	TOP - pixel position boundary of the spot in this scan
#	BOT - pixel position boundary of the spot in this scan
#
# UPDATE ISSUES
#
# By default, the program dies if any of the sub experiments are found in the DB,
# but allows you to add additional experiments for known chips. The program does NOT search for
# known chips in an intelligent way, it just uses taxon and description (ChipDescription).
# It DOES check that the spots in the known chip match the information in the files
# provided, and issues warnings (which you should take very seriously)
# if those do not match.
#
# Use -updateChip 1 if you want to modify chip/spot information for
# the chips in the given set. This will NOT check that the spots match,
# since the point is to let you update the spots. This will only work
# correctly if ALL of the experiments associated with that chip are in
# this set and are also being updated, because it deletes the old rows
# and adds new ones rather than trying to match them up (chipId will
# stay the same but spotId will change).
#
# Use -updateExp 1 if you want to modify experiments which are in the
# database.  (You can also add new sub-experiments in the same run.) Again,
# this actually deletes and adds rather than uploading new ones
# (subExpId will stay the same but expId will change).  These
# experiments will be matched based on the fileName.
#
# Use -curation if you want to update the chips and the experiments
# but do not want to reload any of the data on spots. The SQL this produces
# will run MUCH faster, and this can handle changes to dye assignments,
# which is the only change to the data you would normally need to
# make after the initial load unless something major was wrong (such
# incorrect chip assignments).
#
# In the future, options should be added for both matchings to
# override the default behavior, and when we do updates we should check that
# all of the rows that are being affected are being recreated later, and
# to do curation-only updates for known data and slow updates for new data.
#
# Finally, be warned that the SQL produced by the -curation option
# is NOT idempotent and if something goes wrong or you accidentally
# run it twice you will have to rebuild the slow way (with -updateExp). For SMD experiments
# you can check that the dye assignments are consistent with the original
# by comparing the original files to
# SELECT * FROM ArrayDataAttrs WHERE spotId=N AND subExpId=N AND attr IN ('CH1D_MEAN','CH2D_MEAN');
# SELECT spotId,subExpId,control,treatment FROM ArrayData where spotId=N and subExpId=N;
#
# DATABASE ISSUES
#
# If you use -disableKeys, which is the default, and the SQL fails for some reason,
# then the tables will be left in a disabled state, which is undesriable
#

use lib "../stat";
use lib "../Perl/modules";
use Vector;
use GenomicsUtils;
use Getopt::Long;
use Genome;
use Gene;
use Scaffold;

{
    my $usage = "SMDLoader.pl -taxon taxon -dir directory -table setupTable [-suffix .VIMSS]\n"
	. "   [-updateChip 1 | -updateExp 1 | -curation 1] [-loadADA 1]\n"
	. "   [-host host ] [-disableKeys 1] [-debug 10] > sqlCommands\n";
    my $taxon = "";
    my $dir = "";
    my $setupTableFile = "";
    my $suffix=".VIMSS";
    my $host = undef;
    my $updateChip = 0;
    my $updateExp = 0;
    my $debug = 0;
    my $curation = 0;
    my $disableKeys = 1;
    my $loadADA = 1;

    (GetOptions('taxon=i' => \$taxon,
		'dir=s' => \$dir,
		'table=s' => \$setupTableFile,
		'host=s' => \$host,
		'suffix=s' => \$suffix,
		'updateChip=i' => \$updateChip,
		'updateExp=i' => \$updateExp,
		'curation=i' => \$curation,
		'debug=i' => \$debug,
		'disableKeys=i' => \$disableKeys,
		'loadADA=i' => \$loadADA)
     && @ARGV == 0 && $taxon && $dir && $setupTableFile) || die $usage;

    $updateChip = 1 if $curation;
    $updateExp = 1 if $curation;

    GenomicsUtils::setDebug($debug) if $debug > 0;
    GenomicsUtils::connect($host);

    my $genome = Genome::new("taxonomyId" => $taxon);
    my @genes = $genome->genes();
    my %genes = map { $_->locusId() => $_ } @genes;

    my $table = Vector::readDelim($setupTableFile, "SMDExptid");

    print "# SMD Loader tables and commands for " . $genome->name() . "\n";

    # First, some indices for convenience
    my @SMDexpts = sort keys %{ $table->{"GroupUniq"} };

    # Make ChipInfo, SpotInfo, SpotPos
    my %ChipUniqToId = ();
    my %ChipUniqToKnown = ();
    my %ChipUniqToAttrs = ();
    my %ChipToSpotToId = ();
    my %SpotToOrgRow = ();
    my %ChipUniqToFile = (); # so we can report chip discrepancies clearly
    my $chipId = GenomicsUtils::queryScalar("SELECT MAX(chipId) FROM ChipInfo");
    $chipId = 0 unless defined $chipId;
    my $spotId = GenomicsUtils::queryScalar("SELECT MAX(spotId) FROM SpotInfo");
    $spotId = 0 unless defined $spotId;

    print STDERR "# Setting up ChipInfo, SpotInfo, SpotPos, SpotAttrs\n";
    print "# Maximum known ChipInfo.chipId: $chipId\n";
    print "# Maximum known SpotInfo.spotId: $spotId\n";

    open(CHIPINFO, ">", "$dir/ChipInfo") || die "Cannot write to $dir/ChipInfo";
    if (!$curation) {
	open (SPOTINFO, ">", "$dir/SpotInfo") || die "Cannot write to $dir/SpotInfo";
	open (SPOTPOS, ">", "$dir/SpotPos") || die "Cannot write to $dir/SpotPos";
	open (SPOTATTRS, ">", "$dir/SpotAttrs") || die "Cannot write to $dir/SpotAttrs";
    }

    my @spotattrs = qw(SECTOR SECTORROW SECTORCOL PROW PCOL TOP BOTTOM LEFT RIGHT FAILED);
    foreach my $SMDexpt (@SMDexpts) {
	my $chipUniq = $table->{"ChipUniq"}->{$SMDexpt};
	die "SMD Experiment $SMDexpt has no ChipUniq" if (!defined $chipUniq) || $chipUniq eq "";

	my $type = $table->{"ChipType"}->{$SMDexpt};
	die "Must specify ChipType for chip $chipUniq!" if $type eq "?" || !$type;
	$type = "cDNA" if $type eq "CDNA";
	my $source = $table->{"ChipSource"}->{$SMDexpt};
	die "SMD Experiment $SMDexpt has no ChipSource" if (!defined $source) || $source eq "";
	my $description = $table->{"ChipDescription"}->{$SMDexpt};
	$description = "\\N" if $description eq "";

	if (exists $ChipUniqToId{$chipUniq}) {
            # already know this chip, but check that source and description match the one we found!
	    if (!exists $ChipUniqToAttrs{$chipUniq}
		|| $ChipUniqToAttrs{$chipUniq}{"type"} ne $type
		|| $ChipUniqToAttrs{$chipUniq}{"source"} ne $source
		|| $ChipUniqToAttrs{$chipUniq}{"description"} ne $description) {
		die "ChipUniq $chipUniq does not have consistent type/source/description:\n"
		    . join(":",$type,$source,$description)
		    . " versus\n" . join(":", map { $ChipUniqToAttrs{$chipUniq}{$_} } qw/type source description/);
	    }
	} else {
	    # chip we haven't seen yet
	    # first, see if this chip is already in the database
	    my $result = GenomicsUtils::query("SELECT chipId,source from ChipInfo where taxonomyId=$taxon"
					      . " AND description='$description'");
	    if (scalar @$result > 1) {
		if  ($updateExp && $updateChip) {
		    print STDERR "# Warning: non-unique chip taxon/description combination $taxon:$description"
			. " found in DB - hopefully will be overwritten\n";
		} else {
		    die "Non-unique chip taxon/description combination $taxon:$description for SMDexpt $SMDexpt";
		}
	    }
	    if (scalar @$result > 0) {
		my $dbchip = $result->[0][0];
		my $dbsource = $result->[0][1];
		if ($result->[0][1] ne $source) {
		    my $warning = "# Warning: source $source does not match database source $dbsource"
			. " for chipId $dbchip ChipUniq $chipUniq taxon $taxon description $description:"
			. " OK if curator changed the source\n";
		    print STDERR $warning;
		    print STDOUT $warning;
		}
		print STDERR "# Found known chip $dbchip for taxon $taxon source $source description $description\n";
		$ChipUniqToId{$chipUniq} = $dbchip;
		$ChipUniqToKnown{$chipUniq} = $dbchip;
	    } else {
		die "Unknown chip for taxon $taxon source $source description $description\n" if $curation;
		# Now check to see if it has the same description as any of our known chips
		foreach my $cu (keys %ChipUniqToAttrs) {
		    if ($ChipUniqToAttrs{$cu}{"description"} eq $description) {
			die "ChipUniq $cu and $chipUniq have the same description $description\n";
		    }
		}
		$chipId++;
		$ChipUniqToId{$chipUniq} = $chipId;
		$ChipUniqToFile{$chipUniq} = "$dir/$SMDexpt$suffix";
	    }
	    $ChipUniqToAttrs{$chipUniq} = { "type" => $type, "source" => $source, "description" => $description };

	    print CHIPINFO join("\t",$ChipUniqToId{$chipUniq}, $taxon, $source, $type, $description),"\n";

	    # fill KnownSpots, and parcel out the spotIds afterwards
	    # don't try to maintain spotIds if updating but not curating (b/c spots may have changed)
	    my $useKnownSpots = $ChipUniqToKnown{$chipUniq} && ($curation || !$updateChip);
	    my $knownSpots;
	    if ($useKnownSpots) {
		$knownSpots = query("SELECT spotId, sourceId, seq, sector, sectorRow, sectorCol"
				    . " FROM SpotInfo WHERE chipId=" . $ChipUniqToId{$chipUniq}
				    . " ORDER BY spotId");
	    }
	    
	    # Read the spots, build SpotToOrgRow, and write SpotInfo/SpotAttrs if appropriate:
	    $ChipToSpotToId{$chipUniq} = {};
	    my ($list, $attrs) = Vector::readXLSList("$dir/$SMDexpt$suffix");
	    foreach my $row (@{ $list }) {
		my $spotName = $row->{"SPOT"};
		my $spotSourceName = $row->{"NAME"}; # could also use SOURCE if it is not NULL...
		$spotSourceName = $row->{"SOURCE"} if !defined $spotSourceName || $spotSourceName eq "";
		$spotSourceName = $row->{"SPOT"} if !defined $spotSourceName || $spotSourceName eq "";
		$spotSourceName = $row->{"GENE"} if !defined $spotSourceName || $spotSourceName eq "";
		$spotName = $row->{"GENE"} if !defined $spotName;
		my $seq = "\\N";
		my @knownRow = ();
		if ($useKnownSpots) {
		    die "Too many rows of spots in $dir/$SMDexpt$suffix versus database"
			if $useKnownSpots && scalar @$knownSpots==0;
		    @knownRow = map { defined($_) ? $_ : "\\N" } @{ shift @$knownSpots };
		    $spotId = $knownRow[0];
		} else {
		    $spotId++;
		}
		$SpotToOrgRow{$spotId} = $row;
		$ChipToSpotToId{$chipUniq}{$spotName} = $spotId;
		my $mismatch = "\\N";
		my $verificationCode = $row->{"FAILED"};
		$verificationCode = "" if !defined $row->{"FAILED"};
		$verificationCode = "\\N" if $verificationCode eq ""
		    && (!defined($row->{"IS_VERIFIED"})
			|| $row->{"IS_VERIFIED"} eq ""
			|| $row->{"IS_VERIFIED"} eq "N"
			|| $row->{"IS_VERIFIED"} eq "0");
		$verificationCode = -1 if $verificationCode eq "";
		my $contaminated = $row->{"IS_CONTAMINATED"};
		$contaminated = "\\N" if !(defined $contaminated) || $contaminated eq "U";
		$contaminated = "1" if $contaminated eq "Y";
		$contaminated = "0" if $contaminated eq "N";
		my @sectorPos = ();
		foreach my $name (qw(SECTOR SECTORROW SECTORCOL)) {
		    my $pos = $row->{$name};
		    $pos = "\\N" if !defined($pos) || $pos eq "";
		    push @sectorPos, $pos;
		}

		if ($useKnownSpots) {
		    if ($spotSourceName ne $knownRow[1]
			|| $seq ne $knownRow[2]
			|| $sectorPos[0] ne $knownRow[3]
			|| $sectorPos[1] ne $knownRow[4]
			|| $sectorPos[2] ne $knownRow[5]) {
			print STDERR "# Warning! Chip " . $ChipUniqToId{$chipUniq} . " file $dir/$SMDexpt$suffix: "
			    . "known spots " . join("\t", @knownRow) . " do not match row "
			    . join("\t", $spotId, $ChipUniqToId{$chipUniq}, $spotSourceName, $seq,
				   $mismatch, $verificationCode, $contaminated, @sectorPos) . "\n";
		    }
		}

		if (!$curation && (!exists $ChipUniqToKnown{$chipUniq} || $updateChip)) {
		    print SPOTINFO join("\t", $spotId, $ChipUniqToId{$chipUniq}, $spotSourceName, $seq,
					$mismatch, $verificationCode, $contaminated, @sectorPos), "\n";
		    my $vimss = $row->{"VIMSS"};
		    if ($vimss ne "") {
			die "Spot $spotName has invalid VIMSS id $vimss" if !exists $genes{$vimss};
			print SPOTPOS join("\t", $spotId, "\\N", $vimss), "\n";
		    }
		    foreach my $attr (@spotattrs) {
			if (exists $row->{$attr} && $row->{$attr} ne "") {
			    print SPOTATTRS join("\t", $spotId, $attr, $row->{$attr}), "\n";
			}
		    }
		}
	    } # end for each row
	    die "Too may known spots in database for chip " . $ChipUniqToId{$chipUniq}
	        if $useKnownSpots && scalar @$knownSpots != 0;
	}
    }

    close(CHIPINFO) || die "Cannot close $dir/ChipInfo";
    if (!$curation) {
	close(SPOTINFO) || die "Cannot close $dir/SpotInfo";
	close(SPOTPOS) || die "Cannot close $dir/SpotPos";
	close(SPOTATTRS) || die "Cannot close $dir/SpotAttrs";
    }

    my %GroupToExpId = ();
    my %GroupToFirstSMD = ();
    my $expId = GenomicsUtils::queryScalar("SELECT MAX(expId) FROM ExpInfo");
    $expId = 0 if !defined $expId;

    print STDERR "# Setting up ExpInfo\n";
    print "# Maximum known ExpInfo.expId: $expId\n";

    open(EXPINFO, ">", "$dir/ExpInfo") || die "Cannot write to $dir/ExpInfo";
    foreach my $SMDexpt (@SMDexpts) {
	my $group = $table->{"GroupUniq"}->{$SMDexpt};
	die "SMDexpt $SMDexpt has no GroupUniq" if (!defined $group) || $group eq "";
	if (!exists $GroupToExpId{$group}) {
	    $expId++;
	    $GroupToExpId{$group} = $expId;
	    $GroupToFirstSMD{$group} = $SMDexpt;

	    my $attrs = Vector::readXLSAttrs("$dir/$SMDexpt$suffix");

	    my $chip = $ChipUniqToId{$table->{"ChipUniq"}->{$SMDexpt}};
	    die "SMDexpt $SMDexpt has no chip id" if (!defined $chip) || $chip eq "";
	    my $condition = $table->{"Condition"}->{$SMDexpt};
	    die "Illegal condition for $SMDexpt" if $condition eq "" || $condition eq "?";
	    my $controlInfo = $table->{"ControlInfo"}->{$SMDexpt};
	    die "Illegal control info for $SMDexpt" if $controlInfo eq "" || $controlInfo eq "?";
	    my $treatmentInfo = $table->{"TreatmentInfo"}->{$SMDexpt};
	    die "Illegal treatment info for $SMDexpt" if $treatmentInfo eq "" || $treatmentInfo eq "?";
	    my $pubMedId = $table->{"PubMedID"}{$SMDexpt};
	    $pubMedId = "\\N" if (!defined $pubMedId) || $pubMedId eq "" ;
	    my $citation = $table->{"Citation"}{$SMDexpt};
	    $citation = "\\N" if (!defined $citation) || $citation eq "";
	    my $contact = $attrs->{"Experimenter"};
	    $contact = "\\N" if (!defined $contact) || $contact eq "";
	    my $email = $attrs->{"Contact email"};
	    $email = "\\N" if (!defined $email) || $email eq "";
	    my $comment = $group; # may need to be moved to table...
	    print EXPINFO join("\t", $expId, $chip, $taxon,
			       $condition, $controlInfo, $treatmentInfo, $pubMedId, $citation,
			       $contact, $email, $comment), "\n";
	} else {
	    # Verify that we kept the same chip!
	    my $chipName = $table->{"ChipUniq"}->{$SMDexpt};
	    die "SMDexpt $SMDexpt has no ChipUniq" if (!defined $chipName) || $chipName eq "";
	    $first = $GroupToFirstSMD{$group};
	    die "SMDexpt $SMDexpt has a different chip id from $first in the same set (matching GroupUniq)"
		if $chipName ne $table->{"ChipUniq"}->{$first};
	}
    }
    close(EXPINFO) || die "Cannot close $dir/ExpInfo";

    my $subExpId = GenomicsUtils::queryScalar("SELECT MAX(subExpId) FROM SubExpInfo");
    $subExpId = 0 if !defined $subExpId;

    print STDERR "# Setting up SubExpInfo, SubExpAttrs, ArrayData, ArrayDataAttrs\n";
    print "# Maximum known SubExpInfo.subExpId: $subExpId\n";

    open(SUBEXPINFO, ">", "$dir/SubExpInfo") || die "Cannot write to $dir/SubExpInfo";
    open(SUBEXPATTRS, ">", "$dir/SubExpAttrs") || die "Cannot write to $dir/SubExpAttrs";
    if (!$curation) {
	open(ARRAYDATA, ">", "$dir/ArrayData") || die "Cannot write to $dir/ArrayData";
	open(ARRAYDATAATTRS, ">", "$dir/ArrayDataAttrs") || die "Cannot write to $dir/ArrayDataAttrs";
    }

    # number of technical replicates so far for each biological replicate
    my %GroupBioToN = map { $_ => {} } (keys %GroupToExpId);

    my %fileToKnownSubExp = ();

    my @swapSubExps = ();

    foreach my $SMDexpt (@SMDexpts) {
	my $file = "$dir/$SMDexpt$suffix";
	my $subExpIdThis;
	my $result = GenomicsUtils::queryScalar("SELECT subExpId from SubExpInfo where file='$file'");
	if (defined $result) {
	    die "Sub-experiment from file $file already in the database as subExpId $result\n" unless $updateExp;
	    $fileToKnownSubExp{$file} = $result;
	    $subExpIdThis = $result;
	} else {
	    die "Unknown sub-experiment from file $file\n" if $curation;
	    $subExpId++;
	    $subExpIdThis = $subExpId;
	}

	my $group = $table->{"GroupUniq"}->{$SMDexpt};
	die "SMDexpt $SMDexpt has no GroupUniq" if (!defined $group) || $group eq "";
	my $exp = $GroupToExpId{$group};
	die "SMDexpt $SMDexpt has no expId" if (!defined $exp) || $exp eq "";

	my ($list, $attrs);
	if ($curation) {
	    $attrs = Vector::readXLSAttrs("$file");
	} else {
	    ($list, $attrs) = Vector::readXLSList($file);
	}
	my $biological = $table->{"Biological"}->{$SMDexpt};
	my $technical = "\\N";
	if ($biological eq "?") { # everything NULL - curator didn't figure it out
	    $biological = "\\N";
	} else {
	    die "Biological must be numeric" if (!defined $biological) || !$biological || !($biological =~ m/^[0-9]+$/);
	    $GroupBioToN{$group}{$biological}++;
	    $technical = $table->{"Technical"}->{$SMDexpt};
	    $technical = $GroupBioToN{$group}{$biological} if ($technical eq "?");
	    die "Technical must be numeric" if (!defined $technical) || !$technical || !($technical =~ m/^[0-9]+$/);
	}
	my $controlDye = "\\N";
	my $treatmentDye = "\\N";
	my $treatmentChannel = uc($table->{"TreatmentChannel"}->{$SMDexpt});
	my $swap = 0;
	if ($treatmentChannel eq "RED" || $treatmentChannel eq "CY5" || $treatmentChannel eq "2") {
	    $controlDye = "Cy3";
	    $treatmentDye = "Cy5";
	} elsif ($treatmentChannel eq "GREEN" || $treatmentChannel eq "CY3" || $treatmentChannel eq "1") {
	    $controlDye = "Cy5";
	    $treatmentDye = "Cy3";
	    $swap = 1;
	} elsif ($treatmentChannel eq "?") {
	    ; # nulls
	} else {
	    die "Illegal treatment channel $treatmentChannel for SMDexpt $SMDexpt";
	}
	my $imageId = "\\N";
	print SUBEXPINFO join("\t", $subExpIdThis, $exp, $biological, $technical, $file,
			      $controlDye, $treatmentDye, $imageId), "\n";
	foreach my $attr (keys %{$attrs}) {
	    print SUBEXPATTRS join("\t",$subExpIdThis,$attr,$attrs->{$attr}),"\n";
	}

	my $chipUniq = $table->{"ChipUniq"}{$SMDexpt};
	die "SMD expt $SMDexpt has no chip" if (!defined $chipUniq) || $chipUniq eq "";
	if ($curation) {
	    # test for dye swaps...
	    my $oldTreatmentDye = queryScalar("SELECT treatmentDye FROM SubExpInfo WHERE subExpId=$subExpIdThis");
	    my $oldControlDye = queryScalar("SELECT controlDye FROM SubExpInfo WHERE subExpId=$subExpIdThis");
	    if (!defined $oldTreatmentDye) {
		die "Bad old treatment dye for subExpId=$subExpIdThis" if defined $oldControlDye;
		push @swapSubExps, $subExpIdThis if $swap; # we are opposite of the source file?
	    } elsif ($oldTreatmentDye eq "") {
		die "Bad old control dye for subExpId=$subExpIdThis" if $oldControlDye ne "";
		push @swapSubExps, $subExpIdThis if $swap; # we are opposite of the source file?
	    } elsif ($oldTreatmentDye eq $treatmentDye) {
		die "Bad old control dye for subExpId=$subExpIdThis" if $oldControlDye ne $controlDye;
		# do nothing - dye assignments did not change
	    } elsif ($oldTreatmentDye eq $controlDye) {
		die "Bad old control dye for subExpId=$subExpIdThis" if $oldControlDye ne $treatmentDye;
		push @swapSubExps, $subExpIdThis; # we are opposite of the old annotation
	    } else {
		die "Cannot handle dye swap for subExpId=$subExpIdThis"
		    . " old dye (treat=$oldTreatmentDye, control=$oldControlDye)"
		    . " new dye (treat=$treatmentDye, control=$controlDye)\n";
	    }
	}
	else {
	    foreach my $row (@{ $list }) {
		my $spotName = $row->{"SPOT"};
		$spotName = $row->{"GENE"} if !defined $spotName;
		die "No spot in row " . join("\t",values(%$row)) if (!defined $spotName) || $spotName eq "";
		my $spot = $ChipToSpotToId{$chipUniq}{$spotName};
		die "SMD expt $SMDexpt spotName $spotName has no spot id" if (!defined $spot) || $spot eq "";
		# check that the spots match our stored info
		my $spotRow = $SpotToOrgRow{$spot};
		foreach my $attr (qw/SPOT NAME VIMSS SECTOR SECTORROW SECTORCOL FAILED IS_CONTAMINATED/) {
		    next if (!defined($row->{$attr}) && !defined($spotRow->{$attr}));
		    next if defined($row->{$attr}) && defined($spotRow->{$attr})
			&& $row->{$attr} eq $spotRow->{$attr};
		    die "Attribute $attr for spot named $spotName in subexp file $file does not match"
			. " that in " . $ChipUniqToFile{$chipUniq} . ": "
			. $row->{$attr} . " versus " . $spotRow->{$attr} . "\n";
		}

		# just R & G for now...
		my $control = $row->{"CH1D_MEAN"};
		my $treatment = $row->{"CH2D_MEAN"};
		$control = "" if !defined $control || $control eq "NA";
		$treatment = "" if !defined $treatment || $treatment eq "NA";
		my $log2Ratio = "";
		if (exists $row->{"LOG2RATIO"}) {
		    $log2Ratio = $row->{"LOG2RATIO"};
		    $log2Ratio = "" if $log2Ratio eq "NA";
		} else {
		    $log2Ratio = log($treatment/$control)/log(2) if $control ne "" && $treatment ne ""
			&& $control > 0 && $treatment > 0;
		}
		my $log2RatioNormalized = $row->{"LOG_RAT2N_MEAN"};
		$log2RatioNormalized = "" if !defined $log2RatioNormalized || $log2RatioNormalized eq "NA";
		my $dyeCorr = $row->{"CORR"};
		$dyeCorr = "\\N" if !defined $dyeCorr || $dyeCorr eq "";
		if ($swap) {
		    my $tmp = $control;
		    $control = $treatment;
		    $treatment = $tmp;
		    $log2Ratio = -$log2Ratio if $log2Ratio ne "";
		    $log2RatioNormalized = -$log2RatioNormalized if $log2RatioNormalized ne "";
		}
		$log2Ratio = "\\N" if $log2Ratio eq "";
		$log2RatioNormalized = "\\N" if $log2RatioNormalized eq "";
		$control = "\\N" if $control eq "";
		$treatment = "\\N" if $treatment eq "";
		my @pos = ();
		foreach my $name (qw(LEFT RIGHT TOP BOT)) {
		    my $pos = "\\N";
		    $pos = $row->{$name} if exists $row->{$name} && $row->{$name} ne "";
		    push @pos, $pos;
		}
		print ARRAYDATA join("\t", $spot, $subExpIdThis, $log2Ratio, $log2RatioNormalized,
				     $control, $treatment, $dyeCorr, @pos), "\n";
		foreach my $attr (keys %{ $row }) {
		    print ARRAYDATAATTRS join("\t",$spot, $subExpIdThis, $attr, $row->{$attr}), "\n";
		}
	    }
	}
    }

    close(SUBEXPINFO) || die "Cannot close $dir/SubExpInfo";
    close(SUBEXPATTRS) || die "Cannot close $dir/SubExpAttrs";
    if (!$curation) {
	close(ARRAYDATA) || die "Cannot close $dir/ArrayData";
	close(ARRAYDATAATTRS) || die "Cannot close $dir/ArrayDataAttrs";
    }

    print "use genomics_test;\n";
    print "START TRANSACTION;\n";
    foreach my $chip (values %ChipUniqToKnown) {
	print STDERR "# Warning: updating chip information for chipId $chip\n";
	print STDERR "# Warning: other experiments based on that chip will become inconsistent!\n";
	if ($updateChip && !$curation) {
	    print "DELETE FROM SpotAttrs USING SpotAttrs,SpotInfo WHERE SpotAttrs.spotId=SpotInfo.spotId"
		. " AND SpotInfo.chipId=$chip;\n";
	    print "DELETE FROM SpotPos USING SpotPos,SpotInfo WHERE SpotPos.spotId=SpotInfo.spotId"
		. " AND SpotInfo.chipId=$chip;\n";
	    print "DELETE FROM SpotInfo WHERE chipId=$chip;\n";
	}
	print "DELETE FROM ChipInfo WHERE chipId=$chip;\n"; # always change ChipInfo
    }
    if ($updateExp && scalar(values %fileToKnownSubExp) > 0) {
	my $oldSEs = join(",", sort {$a <=> $b} values %fileToKnownSubExp);
	print STDERR "# Warning: updating experiments related to these subExpIds: $oldSEs\n";
	print STDERR "# Warning: any other subExpIds in the same experiments will become inconsistent\n";
	if (!$curation) {
	    print "#" unless $loadADA;
	    print "DELETE FROM ArrayDataAttrs WHERE subExpId IN ($oldSEs);\n";
	    print "DELETE FROM ArrayData WHERE subExpId IN ($oldSEs);\n";
	}
	print "DELETE FROM SubExpAttrs WHERE subExpId IN ($oldSEs);\n";
	print "DELETE ExpInfo,SubExpInfo FROM ExpInfo,SubExpInfo WHERE SubExpInfo.expId=ExpInfo.expId"
	    . " AND SubExpInfo.subExpId IN ($oldSEs);\n";
    }
    # If we are updating chips but there were no new ones, these files will be empty
    print "LOAD DATA INFILE '" . "$dir/ChipInfo" . "' INTO TABLE"
	. " ChipInfo (chipId, taxonomyId, source, chipType, description);\n";
    if (!$curation) {
	print "LOAD DATA INFILE '" . "$dir/SpotInfo" . "' INTO TABLE SpotInfo"
	    . " (spotId, chipId, sourceId, seq, mismatch, verificationCode, contaminated,"
	    . "  Sector, sectorRow, sectorCol);\n";
	print "LOAD DATA INFILE '" . "$dir/SpotPos" . "' INTO TABLE SpotPos (spotId,posId,locusId);\n";
	print "LOAD DATA INFILE '" . "$dir/SpotAttrs" . "' INTO TABLE SpotAttrs (spotId,attr,value);\n";
    }
    print "LOAD DATA INFILE '" . "$dir/ExpInfo" . "' INTO TABLE ExpInfo"
	. " (expId,chipId,sampleTaxId,condition,controlInfo,treatmentInfo,pubMedId,citation,contact,email,comment);\n";
    print "LOAD DATA INFILE '" . "$dir/SubExpInfo" . "' INTO TABLE SubExpInfo"
	. " (subExpId,expId,biological,technical,file,controlDye,treatmentDye,imageId);\n";
    print "LOAD DATA INFILE '" . "$dir/SubExpAttrs" . "' INTO TABLE SubExpAttrs (subExpId,attr,value);\n";
    if (!$curation) {
	print "ALTER TABLE ArrayData disable keys;\n" if $disableKeys;
	print "LOAD DATA INFILE '" . "$dir/ArrayData" . "' INTO TABLE ArrayData"
	    . " (spotId,subExpId,log2Ratio,log2RatioNormalized,control,treatment,dyeCorr,"
	    . "  leftPix,rightPix,topPix,bottomPix);\n";
	print "ALTER TABLE ArrayData enable keys;\n" if $disableKeys;
	print "#" unless $loadADA;
	print "ALTER TABLE ArrayDataAttrs disable keys;\n" if $disableKeys;
	print "#" unless $loadADA;
	print "LOAD DATA INFILE '" . "$dir/ArrayDataAttrs" . "' INTO TABLE ArrayDataAttrs"
	    . " (spotId,subExpId,attr,value);\n";
	print "#" unless $loadADA;
	print "ALTER TABLE ArrayDataAttrs enable keys;\n" if $disableKeys;
    }
    if (scalar @swapSubExps > 0) {
	my $swapSEs = join(",", @swapSubExps);
	print "CREATE TEMPORARY TABLE SWAPPER(spotId INT(10) unsigned NOT NULL,"
	    . "subExpId INT(10) unsigned NOT NULL, control FLOAT, treatment FLOAT, INDEX(spotId), INDEX(subExpId));\n";
	print "INSERT INTO SWAPPER (spotId, subExpId, control, treatment) SELECT spotId,subExpId,control,treatment"
	    . " FROM ArrayData WHERE subExpId IN ($swapSEs);\n";
	print "UPDATE ArrayData a,SWAPPER s SET a.control = s.treatment, a.treatment = s.control WHERE"
	    . " a.subExpId=s.subExpId AND a.spotId=s.spotId;\n";
	print "DROP TABLE SWAPPER;\n";
	print "UPDATE ArrayData SET log2Ratio = -log2Ratio WHERE subExpId IN ($swapSEs) AND log2Ratio IS NOT NULL;\n";
	print "UPDATE ArrayData SET log2RatioNormalized = -log2RatioNormalized WHERE subExpId IN ($swapSEs) AND log2RatioNormalized IS NOT NULL;\n";
    }
    print "COMMIT;\n";
}
